Reply
Regular Visitor
Posts: 28
Registered: ‎01-24-2018
Accepted Solution

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. 

 


Accepted Solutions
Regular Visitor
Posts: 28
Registered: ‎01-24-2018

Re: PowerBI Cumulative Total by Month within Year (Financial Year)

[ Edited ]

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

Regular Visitor
Posts: 28
Registered: ‎01-24-2018

Re: PowerBI Cumulative Total by Month within Year (Financial Year)

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


All Replies
Super User
Posts: 10,605
Registered: ‎07-11-2015

Re: PowerBI Cumulative Total by Month within Year (Financial Year)

[ Edited ]

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Regular Visitor
Posts: 28
Registered: ‎01-24-2018

Re: PowerBI Cumulative Total by Month within Year (Financial 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

Member
Posts: 46
Registered: ‎10-29-2018

Re: PowerBI Cumulative Total by Month within Year (Financial Year)

Hello @stfox1, did you find the solution? I have the exact same problem.

 

Thank you!

Regular Visitor
Posts: 28
Registered: ‎01-24-2018

Re: PowerBI Cumulative Total by Month within Year (Financial Year)

@DM_BIUnfortunately I never resolved the issue. I ended up splitting the proposed multiyear visualiation into two.

Member
Posts: 46
Registered: ‎10-29-2018

Re: PowerBI Cumulative Total by Month within Year (Financial Year)

Hello @stfox1,

 

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. Smiley Happy
 
Best regards,
 
DM
Regular Visitor
Posts: 28
Registered: ‎01-24-2018

Re: PowerBI Cumulative Total by Month within Year (Financial Year)

[ Edited ]

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

Regular Visitor
Posts: 28
Registered: ‎01-24-2018

Re: PowerBI Cumulative Total by Month within Year (Financial Year)

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