Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
1 year ago

Cumulative forecast DAX support

  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 ...
Need Help
Show and Tell
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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's avatar
vjnvinod
Impactful Individual
1 year ago

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's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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.