Dynamic Running Total

[AishwaryaS]

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.

[amitchandak · Accepted solution]

@AishwaryaS , Try a measure like

calculate(sum(Table[Quantity]), filter(allselected(Table), Table[Material] = max(Table[Material]) && Table[Date] <= max(Table[Date])))

[Brinki]

Hello Amit,

And what about last year?

I'm trying to make it dynamic without year filters.

Thanks,

Stephen

[AishwaryaS]

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.

[AishwaryaS]

Thanks a lot! It worked.