Cumulative forecast DAX support

[vjnvinod]

hi All,

i need a cumulative forecast, from the time where my actuals Ends, as you can see from the below

My current dax is not showing the right output for the month of november it should 15,974,903.87 and for Dec it should be 15,974,903.87+15,974,903.87=31,9498,07.74

however its cumulating from Jan to november and throwing the result in november value

how can i fix this dax?

Cumulative Forecast =

IF(

  [Actuals]=0,

    CALCULATE(

        [Forecast],

        FILTER(

            ALLSELECTED('GroupOPEXCAPEX'),

            'GroupOPEXCAPEX'[DateColumn] <= MAX('GroupOPEXCAPEX'[DateColumn]) &&

            'GroupOPEXCAPEX'[Actual/Budget] = "Forecast"

        )

    ),

    BLANK()

)

Forecast = CALCULATE(SUM('GroupOPEXCAPEX'[Value]),'GroupOPEXCAPEX'[Actual/Budget]="Forecast")

Actuals = CALCULATE(SUM('GroupOPEXCAPEX'[Value]),'GroupOPEXCAPEX'[Actual/Budget]="Actuals")

[grazitti_sapna]

Hi @vjnvinod ,
Your Cumulative Forecast isn't calculating correctly. It's starting from the beginning of the year instead of the last month with actual data. We'll adjust the formula to fix this, ensuring accurate calculations for November and December. revised DAX:

Cumulative Forecast DAX

Cumulative Forecast =
VAR LastActualMonth =
CALCULATE(
MAX('GroupOPEXCAPEX'[DateColumn]),
FILTER(
ALL('GroupOPEXCAPEX'),
[Actuals] > 0
)
)

RETURN
IF(
[Actuals] = 0,
CALCULATE(
SUM('GroupOPEXCAPEX'[Value]),
FILTER(
ALLSELECTED('GroupOPEXCAPEX'),
'GroupOPEXCAPEX'[DateColumn] > LastActualMonth &&
'GroupOPEXCAPEX'[DateColumn] <= MAX('GroupOPEXCAPEX'[DateColumn]) &&
'GroupOPEXCAPEX'[Actual/Budget] = "Forecast"
)
),
BLANK()
)

Explanation:

We're figuring out the last month where we have actual data. Then, we're adding up all the forecast values from that month onwards. To avoid double-counting, we're only showing the forecast for months without actual data.

If I have resolved your question, please consider marking my post as a solution. Thank you!

[Bibiano_Geraldo]

Hi @vjnvinod ,

Please try the bellow DAX measure:

Cumulative Forecast =
VAR LastActualDate = MAXX(
    FILTER('GroupOPEXCAPEX', 'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"),
    'GroupOPEXCAPEX'[DateColumn]
)
RETURN
IF(
    [Actuals] = 0,
    CALCULATE(
        [Forecast],
        FILTER(
            ALLSELECTED('GroupOPEXCAPEX'),
            'GroupOPEXCAPEX'[DateColumn] > LastActualDate &&
            'GroupOPEXCAPEX'[DateColumn] <= MAX('GroupOPEXCAPEX'[DateColumn]) &&
            'GroupOPEXCAPEX'[Actual/Budget] = "Forecast"
        )
    ),
    BLANK()
)

Let me know if it works, if no, please consider to provide no sensitive data sample.

[vjnvinod]

@Bibiano_Geraldo 

i think the issue is with the below part of the code, as Actuals have values like 0, in november till december in the dataset and this max can retun as december, hence the result is coming as blank

VAR LastActualDate = MAXX(
    FILTER('GroupOPEXCAPEX', 'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"),
    'GroupOPEXCAPEX'[DateColumn]

[Bibiano_Geraldo]

Hi @vjnvinod ,

Please try the adjusted measure:

Cumulative Forecast =
VAR LastActualDate = 
    MAXX(
        FILTER(
            ALL('GroupOPEXCAPEX'),
            'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"
        ),
        'GroupOPEXCAPEX'[DateColumn]
    )
RETURN
IF(
    MAX('GroupOPEXCAPEX'[DateColumn]) > LastActualDate,
    CALCULATE(
        [Forecast],
        FILTER(
            ALLSELECTED('GroupOPEXCAPEX'),
            'GroupOPEXCAPEX'[DateColumn] > LastActualDate &&
            'GroupOPEXCAPEX'[DateColumn] <= MAX('GroupOPEXCAPEX'[DateColumn]) &&
            'GroupOPEXCAPEX'[Actual/Budget] = "Forecast"
        )
    ),
    BLANK()
)

Expected Output:
November: Displays 15,974,903.87.
December: Displays the cumulative value: 15,974,903.87 + 15,974,903.87 = 31,949,807.74.
No values are shown for periods with actuals.

[anmolmalviya05]

Hi @vjnvinod, I hope you are doing good,

Please try the below DAX

Cumulative Forecast =
VAR LastActualDate =
MAXX(
FILTER(
'GroupOPEXCAPEX',
'GroupOPEXCAPEX'[Actual/Budget] = "Actuals" &&
NOT(ISBLANK('GroupOPEXCAPEX'[Value]))
),
'GroupOPEXCAPEX'[DateColumn]
)
VAR CurrentDate = MAX('GroupOPEXCAPEX'[DateColumn])
RETURN
IF(
CurrentDate > LastActualDate,
CALCULATE(
SUM('GroupOPEXCAPEX'[Value]),
FILTER(
ALLSELECTED('GroupOPEXCAPEX'),
'GroupOPEXCAPEX'[DateColumn] <= CurrentDate &&
'GroupOPEXCAPEX'[DateColumn] > LastActualDate &&
'GroupOPEXCAPEX'[Actual/Budget] = "Forecast"
)
),
BLANK()
)

[vjnvinod]

@anmolmalviya05 

its throwing all blanks

[Rupak_bi]

HI @vjnvinod 

this can be done by creating an index when actual is blank and then multiplying the forcast with index. Try it. else share the data in plain text so that I can copy and build the logic.

[Rupak_bi]

Hi @vjnvinod ,

Please see below result in a calculated column. Hope this solves your Purpose.

if this works, please accept as solution.