Cumulative Total with Max Date Filter

[Anonymous]

Hi 

I'm very new to PowerBi and need some help with the Cumulative Sum Dax formula. I have the following table of Measures created to show Cumulative Sales for current year (Actual), LY and Budget. The data and formula appears correct.

Measures for Cumulative Sales

However, when I select a specific Month in the slicer, eg Apr, I get incorrect figures for the cumulative totals.

I'm not sure what is wrong and hope someone can help me with this. I've used the following measure for Sales Actual and YTD Sales Actual:

Sales Actual = CALCULATE(SUM(SalesData[Sales]),FILTER(SalesData,SalesData[Scenario]="Actual"))

YTD Sales Actual = CALCULATE([Sales Actual],FILTER(ALLSELECTED(Dates),Dates[Date] <= MAX(Dates[Date])))

Many Thanks.

[avanderschilden · Accepted solution]

Hello,

Your cumulative measure should be something like this;

Cumulative Sales Amount =
VAR _MaxDate =
    MAX ( 'Dates'[Date] )
VAR _Result =
    CALCULATE ( [Sales Actual], 'Calendar'[Date] <= _MaxDate )
RETURN
    _Result

[Anonymous · Accepted solution]

This is now solved. I changed from ALLSELECTED to ALL. 

A similar issued was already solved in attached.

 https://community.powerbi.com/t5/Desktop/Slicer-selection-changes-the-cumulative-total/td-p/655657.

I managed to solve this by changing from ALLSELECTED to ALL.  My apologies, I should have reviewed existing forum posts more thoroughly. Thanks

[avanderschilden · Accepted solution]

Hello,

Your cumulative measure should be something like this;

Cumulative Sales Amount =
VAR _MaxDate =
    MAX ( 'Dates'[Date] )
VAR _Result =
    CALCULATE ( [Sales Actual], 'Calendar'[Date] <= _MaxDate )
RETURN
    _Result

[Anonymous]

Thank you avendarschildren. I've tried your solution and it has worked!