Forum Discussion

vjnvinod's avatar
vjnvinod
Icon for Impactful Individual rankImpactful 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 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")

 

 

 

Need Help
Show and Tell

8 Replies

  • anmolmalviya05's avatar
    anmolmalviya05
    Icon for Super User rankSuper User
    1 year ago

    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()
    )

      • Rupak_bi's avatar
        Rupak_bi
        Icon for Super User rankSuper User
        1 year ago

        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.

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper 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
      Icon for Impactful Individual rankImpactful 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
        Icon for Super User rankSuper 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.

         

         

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    1 year ago

    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!