Calculate forecast based on increase and previous calculated month result

[LostInTheFlood]

Hi smart people,

I have issues with a calculations and need your help.

I’m trying to do a “total + forecast” measure (in the picture called Total).

Where I have actuals I will show the actual and where I have a forecast I will show the calculated forecast (total).

The problem is that I have the “delta increase” only, as a separate column, per period and I cannot figure out how to calculate the “running total” based on the previous months calculated value.

I the picture I need to take:

2022-05: 3734+202 = 3936 (actual + forecast=total)

2022-06: 3936+72 = 4008 (total + forecast=total)

Etc

Etc

When I have actuals for 2022-05 that will be the total.

Any ideas are welcome.

Data I have	Data I have	Data I have	Data I need to calculate
Period	Actual	Forecast Increase	Total
2022-01	3651		3651
2022-02	3688		3688
2022-03	3710		3710
2022-04	3734		3734
2022-05		202	3936
2022-06		72	4008
2022-07		17	4025
2022-08		10	4035
2022-09		17	4052
2022-10		1	4053
2022-11		0	4053
2022-12		1	4054

[DataInsights · Accepted solution]

@LostInTheFlood,

Try these measures. I made the Period column a date by adding the first day of the month (2022-01 becomes 2022-01-01). This will enable you to create a relationship with the date table. Alternatively, you could create a Period Number column with values like 202201, and use Period Number instead of Period in the Running Total measure.

Actual = SUM ( FactTable[Actual] )

Forecast = SUM ( FactTable[Forecast] )

Running Total = 
VAR vLastActual =
    CALCULATE (
        LASTNONBLANKVALUE ( FactTable[Actual], [Actual] ),
        ALLSELECTED ( FactTable )
    )
VAR vResult =
    IF (
        MAX ( FactTable[Actual] ) <> BLANK (),
        [Actual],
        vLastActual
            + CALCULATE (
                [Forecast],
                FILTER (
                    ALLSELECTED ( FactTable ),
                    FactTable[Period] <= MAX ( FactTable[Period] )
                )
            )
    )
RETURN
    vResult

[LostInTheFlood]

Hi @DataInsights 
When I tried on my "real" file I got the last actual value as the 2022-11 value.
So I started to prepare a dummy file to upload (hope the link works) and then I acutally got the 2022-10 value in 2022-11. BUT the calculation took into account all previous forcast values where I have actuals. 
I need a vacation 🙂

Sample Data File 

[DataInsights · Accepted solution]

@LostInTheFlood,

Try this measure. I adjusted the DAX to handle months where both Actual and Forecast exist.

Running Total = 
VAR vLastActual =
    CALCULATE (
        LASTNONBLANKVALUE ( Actual[Actual Value], [ActualValue] ),
        ALLSELECTED ( Actual )
    )
VAR vLastActualDate =
    CALCULATE (
        LASTNONBLANK ( 'Date'[Date], [ActualValue] ),
        ALLSELECTED ( Actual )
    )
VAR vResult =
    IF (
        MAX ( Actual[Actual Value] ) <> BLANK (),
        [ActualValue],
        vLastActual
            + CALCULATE (
                [ForecastValue],
                FILTER (
                    ALLSELECTED ( 'Date' ),
                    'Date'[Date] <= MAX ( 'Date'[Date] )
                        && 'Date'[Date] > vLastActualDate
                )
            )
    )
RETURN
    vResult

Now you can take that vacation. 🙂

[LostInTheFlood]

@DataInsights What can I say but thank you! 
Thank you so much for thaking the time. This was exactly what I needed. 

[DataInsights]

@LostInTheFlood,

Glad to hear that worked!

[DataInsights · Accepted solution]

@LostInTheFlood,

Try these measures. I made the Period column a date by adding the first day of the month (2022-01 becomes 2022-01-01). This will enable you to create a relationship with the date table. Alternatively, you could create a Period Number column with values like 202201, and use Period Number instead of Period in the Running Total measure.

Actual = SUM ( FactTable[Actual] )

Forecast = SUM ( FactTable[Forecast] )

Running Total = 
VAR vLastActual =
    CALCULATE (
        LASTNONBLANKVALUE ( FactTable[Actual], [Actual] ),
        ALLSELECTED ( FactTable )
    )
VAR vResult =
    IF (
        MAX ( FactTable[Actual] ) <> BLANK (),
        [Actual],
        vLastActual
            + CALCULATE (
                [Forecast],
                FILTER (
                    ALLSELECTED ( FactTable ),
                    FactTable[Period] <= MAX ( FactTable[Period] )
                )
            )
    )
RETURN
    vResult

[LostInTheFlood]

@DataInsights 
Worked like a charm. Thank you. 

One question. If e.g. 2022-11 is a blank / null value and not zero in the forecast.
How can I obtain the same result, mening 2022-11 = 4053.

[DataInsights]

@LostInTheFlood,

Glad to hear that worked. I changed the 2022-11 forecast to blank/null and got the same result. Are you getting a different result?