topic Re: Cumulative Total with Max Date Filter in DAX Commands and Tips

Cumulative Total with Max Date Filter

Anonymous — Sun, 18 Aug 2019 05:28:41 GMT

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.

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.

Re: Cumulative Total with Max Date Filter

avanderschilden — Sun, 18 Aug 2019 10:43:41 GMT

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

Re: Cumulative Total with Max Date Filter

Anonymous — Sun, 18 Aug 2019 10:58:06 GMT

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

Re: Cumulative Total with Max Date Filter

Anonymous — Sun, 18 Aug 2019 11:19:19 GMT

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