topic Re: Dynamic Running Total in DAX Commands and Tips

Dynamic Running Total

AishwaryaS — Thu, 04 Mar 2021 13:16:08 GMT

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.

Re: Dynamic Running Total

amitchandak — Thu, 04 Mar 2021 14:34:09 GMT

AishwaryaS , Try a measure like

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

Re: Dynamic Running Total

AishwaryaS — Thu, 04 Mar 2021 14:45:30 GMT

Thanks a lot! It worked.

Re: Dynamic Running Total

AishwaryaS — Sun, 07 Mar 2021 13:01:27 GMT

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.

Re: Dynamic Running Total

Brinki — Sun, 20 Nov 2022 10:50:03 GMT

Hello Amit,

And what about last year?

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

Thanks,

Stephen