Forum Discussion

RHerrera's avatar
RHerrera
Frequent Visitor
7 years ago
Solved

Calculated cumulative column with a complication

Hi, I have been trying to get cumulative values in a calculated column but I couldn't find a way to re-start the cumulative calculation once there is a change from Q1 to Q2 to Q3 (Q=quarter of a day). I need the Cumulative column in Power BI, see below (dummy data):

 

Q             Time              Value     Cummulative

16:00:00 AM387387
16:00:00 AM141528
16:00:00 AM4991,027
16:00:00 AM01,027
16:00:00 AM421,069
16:00:00 AM1821,251
16:05:46 AM3511,602
16:06:58 AM5692,171
16:08:48 AM782,249
16:09:35 AM5322,781
16:24:07 AM802,861
16:24:33 AM4613,322
16:25:37 AM993,421
16:27:24 AM03,421
16:30:42 AM2433,664
26:31:36 AM518518
26:43:06 AM80598
26:43:49 AM234832
26:48:46 AM5501,382
27:01:13 AM501,432
27:03:29 AM2241,656
27:07:41 AM1711,827
27:15:10 AM1481,975
27:15:43 AM01,975
27:17:49 AM4512,426
27:18:30 AM812,507
27:19:40 AM1132,620
27:21:32 AM462,666
27:25:37 AM2392,905
27:26:48 AM2203,125
37:26:50 AM496496
37:26:54 AM418914
37:35:13 AM861,000
37:49:16 AM1251,125
37:49:27 AM421,167
37:54:37 AM821,249
37:55:50 AM1811,430
38:00:50 AM3441,774

 

I can't get the fourth column in Power BI.

 

Need your help!

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    RHerrera

     

    try this one

     

    =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( Table1, [Q] = EARLIER ( [Q] ) && [Time] <= EARLIER ( [Time] ) )
)

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    RHerrera

     

    try this one

     

    =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( Table1, [Q] = EARLIER ( [Q] ) && [Time] <= EARLIER ( [Time] ) )
)
    • RHerrera's avatar
      RHerrera
      Frequent Visitor
      7 years ago

      Thank you very much, I was really close but you got it!

  • RHerrera's avatar
    RHerrera
    Frequent Visitor
    7 years ago

    Hi, I have been trying to get cumulative values in a calculated column but I couldn't find a way to re-start the cumulative calculation once there is a change from Q1 to Q2 to Q3 (Q=quarter of a day). I need the Cumulative column in Power BI, see below (dummy data):

     

    Q             Time              Value     Cummulative

    16:00:00 AM387387
    16:00:00 AM141528
    16:00:00 AM4991,027
    16:00:00 AM01,027
    16:00:00 AM421,069
    16:00:00 AM1821,251
    16:05:46 AM3511,602
    16:06:58 AM5692,171
    16:08:48 AM782,249
    16:09:35 AM5322,781
    16:24:07 AM802,861
    16:24:33 AM4613,322
    16:25:37 AM993,421
    16:27:24 AM03,421
    16:30:42 AM2433,664
    26:31:36 AM518518
    26:43:06 AM80598
    26:43:49 AM234832
    26:48:46 AM5501,382
    27:01:13 AM501,432
    27:03:29 AM2241,656
    27:07:41 AM1711,827
    27:15:10 AM1481,975
    27:15:43 AM01,975
    27:17:49 AM4512,426
    27:18:30 AM812,507
    27:19:40 AM1132,620
    27:21:32 AM462,666
    27:25:37 AM2392,905
    27:26:48 AM2203,125
    37:26:50 AM496496
    37:26:54 AM418914
    37:35:13 AM861,000
    37:49:16 AM1251,125
    37:49:27 AM421,167
    37:54:37 AM821,249
    37:55:50 AM1811,430
    38:00:50 AM3441,774

     

    I can't get the fourth column in Power BI.

     

    Need your help!