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

Many thanks for your insight. It worked! I would like to ask if the calculation also based on ID, for example:

 

Cumulative sum of ID 11 by date 22/06 = total[ID11 in date 20/06] + total[ID11 in date 21/06] + total [ID11 in date 22/06]

(without including other ID like ID17, ID26, etc in date 20/06)

 

DateIdTotal 
20/06/2016111 
20/06/2016172 
20/06/2016264 
21/06/20161795 
21/06/2016117 
21/06/20161848 
21/06/201629856 
22/06/2016102558 
22/06/20161159 =1+7+59
22/06/2016103061 
22/06/20162624211 
22/06/20162682212 
22/06/20162752214 
22/06/20162772215 
23/06/201611217 
23/06/201617218 
23/06/201628220 

 

I am grateful very much for your help!

 

Best regards,

KGrice's avatar
KGrice
Icon for Memorable Member rankMemorable Member
10 years ago

Glad to help! You can extend the previous measure to include the Id by adding another filter, like so:

 

MovingSumByID = CALCULATE(SUM(TableName[Total]), 
	FILTER(ALL(TableName), TableName[Date] > MAX(TableName[Date])-2), 
	FILTER(ALL(TableName), TableName[Date] <= MAX(TableName[Date])), 
	FILTER(ALL(TableName), TableName[Id]=MAX(TableName[Id]))
	)
  • jahida's avatar
    jahida
    Icon for Impactful Individual rankImpactful Individual
    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.