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 want to plot cumulative totals (Actual vs Plan) by month within each year (Actually financial year starting in each July). I have the basic cumulative total working across all months- See Figure 1.
But I am struggling to implement a filter that allows users to view the cumulative total for each financial year separately (i.e. with the year starting with a cumulative Actuals and Plans values being zero).
At the moment - if I select the second financial year with the slicer, the cumulative total doesn't start at zero (See figure 2)
Can anyone suggest a fix? I am not sure if the solution is linked to the Filter/ Slicer behavior or the DAX calculation.
Figure 1 : Cumulative totals by month across all years
Figure 2 : Cumulative totals for second Year. Issue as year doesn't start off with Plans and Actuals being zero
My current cumulative total calculations (for Actuals & Plan) are here
Cumulative Actuals = IF ( MIN ( 'Date Dim'[BOM] ) <= CALCULATE ( MAX ( 'Detailed Breakdown'[Actuals_BOMS]), ALL ('Detailed Breakdown') ), CALCULATE ( SUM ( 'Detailed Breakdown'[Actual($)]), FILTER ( ALL ( 'Date Dim'[BOM] ), 'Date Dim'[BOM] <= MAX ( 'Date Dim'[BOM] ) ) )) Cumulative Plan = CALCULATE ( SUM ( 'Detailed Breakdown'[Plan($)]), FILTER ( ALL ( 'Date Dim'[BOM] ), 'Date Dim'[BOM] <= MAX ( 'Date Dim'[BOM] ) ) )
A copy of my PBI file is located here
Any help is appreciated.
Solved! Go to Solution.
Solution Provided in video & associated blog post below. Working PBI desktop example in link.
https://drive.google.com/file/d/1IwH5f1tH-FC06wuQUiB-8xxRN0Gb9Iae/view?usp=sharing
Examples from linked PBI file
1a) Cumulative totals by Financial Years (2017-2019), 1b) Cumulative Totals in (2018/2019) 1c) Cumulative Totals (2017/2018) . Controlled by Finanical Year Slicer on Visualiation.
Solution Provided in video & associated blog post below. Working PBI desktop example in link.
https://drive.google.com/file/d/1IwH5f1tH-FC06wuQUiB-8xxRN0Gb9Iae/view?usp=sharing
Examples from linked PBI file
1a) Cumulative totals by Financial Years (2017-2019), 1b) Cumulative Totals in (2018/2019) 1c) Cumulative Totals (2017/2018) . Controlled by Finanical Year Slicer on Visualiation.
Solution Provided in video & associated blog post below. Working PBI desktop example in link.
https://drive.google.com/file/d/1IwH5f1tH-FC06wuQUiB-8xxRN0Gb9Iae/view?usp=sharing
Examples from linked PBI file
1a) Cumulative totals by Financial Years (2017-2019), 1b) Cumulative Totals in (2018/2019) 1c) Cumulative Totals (2017/2018) . Controlled by Finanical Year Slicer on Visualiation.
Solution Provided in video & associated blog post below. Working PBI desktop example in link.
https://drive.google.com/file/d/1IwH5f1tH-FC06wuQUiB-8xxRN0Gb9Iae/view?usp=sharing
Examples from linked PBI file
1a) Cumulative totals by Financial Years (2017-2019), 1b) Cumulative Totals in (2018/2019) 1c) Cumulative Totals (2017/2018) . Controlled by Finanical Year Slicer on Visualiation.
You probably need something along these lines.
Cumulative Actuals = VAR __Year = MAX('Date Dim'[BOM]) RETURN IF ( MIN ( 'Date Dim'[BOM] ) <= CALCULATE ( MAX ( 'Detailed Breakdown'[Actuals_BOMS]), ALL ('Detailed Breakdown') ), CALCULATE ( SUM ( 'Detailed Breakdown'[Actual($)]), FILTER ( ALL ( 'Date Dim'[BOM] ), 'Date Dim'[BOM] <= MAX ( 'Date Dim'[BOM]) && YEAR('Date Dim'[BOM]=__Year ) )) Cumulative Plan = VAR __Year = MAX('Date Dim'[BOM]) RETURN CALCULATE ( SUM ( 'Detailed Breakdown'[Plan($)]), FILTER ( ALL ( 'Date Dim'[BOM] ), 'Date Dim'[BOM] <= MAX ( 'Date Dim'[BOM]) && YEAR('Date Dim'[BOM]=__Year ) )
Thanks for the suggestion Greg. However, the solution still doesn't appear to enable the correct filtering. I have a link to the PBIX file in the original post if that helps. Many thanks Steve
Hello @Anonymous, did you find the solution? I have the exact same problem.
Thank you!
@DM_BIUnfortunately I never resolved the issue. I ended up splitting the proposed multiyear visualiation into two.
Hello @Anonymous,
Thank you for your answer. I think I've found the solution. We have to use ALLSELECTED instead of ALL because ALLSELECTED retains the filters. For me it works perfectly with this formula :
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |