Forum Discussion
cumulative running sum
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])))
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]))
)
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])))
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 result Explanation of Result 20/06/2016 11 1 1 sum 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 9 sum 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 56 Result 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 70 sum 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!
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.
