Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@AishwaryaS , Try a measure like
calculate(sum(Table[Quantity]), filter(allselected(Table), Table[Material] = max(Table[Material]) && Table[Date] <= max(Table[Date])))
@AishwaryaS , Try a measure like
calculate(sum(Table[Quantity]), filter(allselected(Table), Table[Material] = max(Table[Material]) && Table[Date] <= max(Table[Date])))
Hello Amit,
And what about last year?
I'm trying to make it dynamic without year filters.
Thanks,
Stephen
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.
Thanks a lot! It worked.
User | Count |
---|---|
53 | |
35 | |
20 | |
15 | |
14 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |