Forum Discussion
Dynamic Running Total
Hi, I want to calculate cumulative sum of quantity based on each material considering date, ID and Item columns, and also while a slicer filter is selected the cumulative sum must dynamically change accordingly. Below is the scenario:
| Material | ID | Item | Date | Quantity | Cumulative Sum |
| Mobile | 103 | 1 | 01/01/2021 | 2 | 2 |
| Mobile | 103 | 2 | 01/01/2021 | 16 | 18 |
| TV | 100 | 1 | 01/01/2021 | 10 | 10 |
| TV | 101 | 3 | 02/01/2021 | 5 | 15 |
| TV | 102 | 6 | 03/01/2021 | 15 | 30 |
I could achieve it using the below SQL query but unable to do it dynamically using DAX
SELECT *,SUM(Quantity) OVER (PARTITION BY MATERIAL ORDER BY DATE , ID , ITEM ASC) AS Cumulative_Sum
Any help? Thanks in advance.
AishwaryaS , Try a measure like
calculate(sum(Table[Quantity]), filter(allselected(Table), Table[Material] = max(Table[Material]) && Table[Date] <= max(Table[Date])))
4 Replies
AishwaryaS , Try a measure like
calculate(sum(Table[Quantity]), filter(allselected(Table), Table[Material] = max(Table[Material]) && Table[Date] <= max(Table[Date])))
Thanks a lot! It worked.
Hi amitchandak , I applied the above formula and the cumulative sum came correct for different dates but for the same dates the quantity was getting grouped by like below. Could you please let me know how to attain the cumulative sum - as expected?
Material Date Item ID Quantity Cumulative Sum I got Cumulative Sum - expected TV 01/04/2018 4 143 -100 -100 -100 TV 06/04/2018 1 146 -280 -380 -380 TV 12/04/2018 3 513 34 -313 -346 TV 12/04/2018 2 511 33 -313 -313 TV 25/04/2018 17 178 -50 -363 -363 Thanks in advance.
Hello Amit,
And what about last year?I'm trying to make it dynamic without year filters.
Thanks,
Stephen
