Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

PowerBI Cumulative Total by Month within Year (Financial Year)

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. 

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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.

All Years.JPGOne FY (Partial).JPGFirst FY.JPG

View solution in original post

Anonymous
Not applicable

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.

All Years.JPGOne FY (Partial).JPGFirst FY.JPG

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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.

All Years.JPGOne FY (Partial).JPGFirst FY.JPG

Anonymous
Not applicable

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.

All Years.JPGOne FY (Partial).JPGFirst FY.JPG

Greg_Deckler
Super User
Super User

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
    )
)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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!

Anonymous
Not applicable

@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 : 

Cumulative Total = CALCULATE(SUM('MAT 18+19'[FACTURACIÓN NETA]);FILTER(ALLSELECTED(DimDate);'DimDate'[Date] <= MAX('DimDate'[Date]))
 
I hope it helps. 🙂
 
Best regards,
 
DM

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.