Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cumulative Total with Max Date Filter

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.

 

3 Replies

  • avanderschilden's avatar
    avanderschilden
    Icon for Resolver I rankResolver I
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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