Forum Discussion

LostInTheFlood's avatar
LostInTheFlood
Frequent Visitor
4 years ago
Solved

Calculate forecast based on increase and previous calculated month result

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

 

Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    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

     

     

  • DataInsights's avatar
    DataInsights
    4 years ago

    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. 🙂

7 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      LostInTheFlood
      Frequent Visitor
      4 years ago

      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's avatar
        DataInsights
        Icon for Super User rankSuper User
        4 years ago

        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?

         

         

  • LostInTheFlood's avatar
    LostInTheFlood
    Frequent Visitor
    4 years ago

    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's avatar
      DataInsights
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        LostInTheFlood
        Frequent Visitor
        4 years ago

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