Forum Discussion

BusinessAnalyst's avatar
BusinessAnalyst
Icon for Helper I rankHelper I
10 years ago
Solved

cumulative running sum

Dear experts, I would like to ask solutions to create a column which summarizes cumulatively value in column "Total" based on column "Date", in 3-day period, for example: Cumulative sum of "Total" ...
  • jahida's avatar
    jahida
    10 years ago

    KGrice 's solution will work for sure, seems a bit of overkill to have 3 filter clauses for this though. Here's a compressed alternative with the same result:

     

    MovingSum = CALCULATE(SUM(Table2[Total]),
		FILTER(ALLEXCEPT(Table2, Table2[Id]), Table2[Date] > MAX(Table2[Date])-2 && Table2[Date] <= MAX(Table2[Date])))
BusinessAnalyst's avatar
BusinessAnalyst
Icon for Helper I rankHelper I
10 years ago

Dear experts,

 

I tried both and see that:

KGrice's solution return to the same value in each row.

 

And jahida's solution (MovingSum) doesn't return to desired result.

 

My wish is as below:

Date  Id  Total  Desired resultExplanation of Result
20/06/2016  11  1  1sum total {(ID11, date 20/6) + (ID11, date 19/6) + (ID11, date 18/06)}. The result should be = 1 + 0 + 0 = 1, because there is no data of date 19/06 and date 18/06
20/06/2016  17  2  2 
20/06/2016  26  4  4 
21/06/2016  179  5  5 
21/06/2016  183  7  7 
21/06/2016  11  8  9sum total {(ID11, date 21/6) + (ID11, date 20/6) + (ID11, date 19/06)}. The result should be = 8 + 1 + 0 = 9, because there is no date of date 19/06
21/06/2016  298  56  56Result here = 56 = 56 + 0 + 0 because there is only data in date 21/06 (=56) no other date of ID298 in 20/06 and 19/06
22/06/2016  1025  58  58 
22/06/2016  1028  59  59 
22/06/2016  11  61  70sum total {(ID11, date 22/6) + (ID11, date 21/6) + (ID11, date 20/06)}. The result should be = 61 + 8 + 1 = 70
22/06/2016  2624  211  211 
22/06/2016  2682  212  212 


 
Hope it can be solved! Many thanks to your great contribution! 

Cheers!

 

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
10 years ago

BusinessAnalyst

 

Hi I Try the Jahida's dax with your sample data and it Works.

 

Is the same value total and moving sum because your sample data don't have id with 3 consecutive days. I just modified the data with one row for ID 11 in 22/06/16 to test it.