Forum Discussion

LaurentC's avatar
LaurentC
Frequent Visitor
3 years ago
Solved

Cumulative Value Not Totalling Correctly

I have an issue where my cumulative measure is not properly totalling everything in the measure which it is using.

 

I have a dataset with "Plan" and "Actual" data, by month. I am creating a "Forecast" measure which checks for the latest date entry in the actual table, and returns "Actual" numbers if it is before that date, and uses the "Budget" data if it is after that date. The "Forecast" measure is seemingly working correctly, but when I try to make a cumulative measure off of that one, it does not work.

 

Sample table below:

Note that while that while the "Forecast Sales" properly switches to using the "Planned Sales" when there are no more "Actual Sales", the cumulative measure ceases working. I've pared my cumulative measure down to its simplest form and it still doesn't work, so I believe my error was in the "Forecast Sales" measure but I have no idea what the issue might be.

 

Formulas:

 

Forecast Sales =

VAR plotstartdate = MIN('Calendar'[Date])
VAR actualsenddate = CALCULATE(MAX('Actuals'[Date]),ALL('Calendar'))
RETURN
if( actualsenddate >= plotstartdate,
    CALCULATE( SUm(Actuals[Actual Sales])),
    CALCULATE( SUm(Plan[Planned Sales])))

 

 

Forecast Sales (cumulative) =

CALCULATE( [Forecast Sales],
'Calendar'[Date] <= MAX('Calendar'[Date]))
 
 
I've been going around in circles on this one, is there anyone who could help clear this up for me?
 
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    LaurentC,

     

    Try rewriting the measure like this:

     

    Forecast Sales = 
VAR vActualsEndDate =
    CALCULATE ( MAX ( 'Actuals'[Date] ), ALL ( 'Calendar' ) )
VAR vPlannedSales =
    CALCULATE (
        [Planned Sales],
        KEEPFILTERS ( 'Calendar'[Date] > vActualsEndDate )
    )
RETURN
    [Actual Sales] + vPlannedSales

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    LaurentC,

     

    Try rewriting the measure like this:

     

    Forecast Sales = 
VAR vActualsEndDate =
    CALCULATE ( MAX ( 'Actuals'[Date] ), ALL ( 'Calendar' ) )
VAR vPlannedSales =
    CALCULATE (
        [Planned Sales],
        KEEPFILTERS ( 'Calendar'[Date] > vActualsEndDate )
    )
RETURN
    [Actual Sales] + vPlannedSales