Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |