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.
I've attempted to follow the Ferrari / Russo DAX pattern for cumulative sum, where they suggest (based on their own sample data):
Cumulative Quantity = IF(MIN('Date'[Date]) <= CALCULATE(MAX(Transactions[Date]), ALL (Transactions)),
CALCULATE(SUM(Transactions[Quantity]), FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date] ))))
The problem is that, when you use a slicer from the Date table (after joining it to Transactions) to filter a matrix visualisation down to 2013 only, the Cumulative Quantity measure is bringing through the SUM(Transactions[Quantity]) for all of the previous year and adding the 2012 amount to Jan 2013. This is shown in the two images below, showing before and after filtering. How can I change the above DAX so that Jan 2013 will show 3 for the Cumulative Quantity measure, rather than 5 (carrying on the 2012 total)? I need a way for the cumulative measure to effectively start from what is sliced, not the whole Date table. I've tried ALLSELECTED and other things -- I've got a feeling there's a second FILTER statement you could add to the second CALCULATE to do it, but I've had no luck so far.
Solved! Go to Solution.
ALLSELECTED shall work. Try
Cumulative Quantity = IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Transactions[Date] ), ALLSELECTED ( Transactions ) ), CALCULATE ( SUM ( Transactions[Quantity] ), FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) )
Check a demo in the attached pbix.
ALLSELECTED shall work. Try
Cumulative Quantity = IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Transactions[Date] ), ALLSELECTED ( Transactions ) ), CALCULATE ( SUM ( Transactions[Quantity] ), FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) )
Check a demo in the attached pbix.
Thanks for the quick response on that; my problem was omitting ALLSELECTED from the first part of the formula before the 2nd CALCULATE. Nice work.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |