Forum Discussion

RasmusN532's avatar
RasmusN532
Icon for Helper I rankHelper I
1 year ago
Solved

Dynamic Actuals with Forecast

Hi,

I´ve been struggling with this for approx. two weeks now so hopefully someone here can help me.

 

Problem:

I have a table (which is combined by 2 separate tables) FC_GL_Table which is Forecasttable + GL Table(actuals).

I´ve tried to simplify the Data table as below. 

As I´ve tried to show below is that when I select ForecastID = FC2501
I want GL from 2025-01-01 -> 2025-02-28, so basicily < 2025-03-01 and Forecast_part = GL  (as shown in the support table)
And then take Amount where FC part = FC and Forecast_ID = SelectedValue(ForecastID).

 

A forecast have values for 13 months ahead. 

TLDR: Need a measure or solution so I can have Actuals + Forecast based on which Forecast_ID I choose.

 

 


And to support this table I have this map table:

 

I will provide  the measure I´ve been playing around with.

Forecast_Amount = 

VAR SelectedFCStartPeriod = LOOKUPVALUE(FC_StartPeriod[FCStartPeriod], FC_StartPeriod[Forecast_ID], SELECTEDVALUE(FC_GL_TABLE[Forecast_ID]))

VAR SelectedYear = LOOKUPVALUE(FC_StartPeriod[FCStartYear], FC_StartPeriod[Forecast_ID], SELECTEDVALUE(FC_GL_TABLE[Forecast_ID]))

VAR Forecast = CALCULATE(

    SUM(FC_GL_TABLE[Daily_Amount]),

    FC_GL_TABLE[Forecast_ID] = SELECTEDVALUE(FC_GL_TABLE[Forecast_ID]),

    FC_GL_TABLE[FC_Part] <> "GL"

)

VAR Actuals = CALCULATE(

    SUM(FC_GL_TABLE[Daily_Amount]),

    FC_GL_TABLE[FC_Part] = "GL",

    FC_GL_TABLE[Date] < SelectedFCStartPeriod,

    ALLSELECTED(FC_GL_TABLE) )

RETURN

Actuals + Forecast

And this measure wont show any of the actuals, just the Forecast amount, unfortunately (FCStartPeriod = FCStartDate)

 

Let me know if I need to add additional information.

 

Kind regards,

Rasmus

"Actuals"
"DAX"
"Forecast"
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi RasmusN532 ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    Create Measure with below DAX :

    Forecast_Amount_With_Actuals =
    VAR SelectedForecastID = SELECTEDVALUE(FC_GL_TABLE[Forecast_ID])
    VAR SelectedStartDate =
    LOOKUPVALUE(
    FC_StartPeriodID_TABLE[FCStartDate],
    FC_StartPeriodID_TABLE[Forecast_ID], SelectedForecastID
    )

    VAR Actuals =
    CALCULATE(
    SUM(FC_GL_TABLE[Amount]),
    FC_GL_TABLE[Forecast_Pa] = "GL",
    FC_GL_TABLE[Date] < SelectedStartDate,
    REMOVEFILTERS(FC_GL_TABLE[Forecast_ID])
    )

    VAR Forecast =
    CALCULATE(
    SUM(FC_GL_TABLE[Amount]),
    FC_GL_TABLE[Forecast_Pa] = "FC",
    FC_GL_TABLE[Forecast_ID] = SelectedForecastID
    )

    RETURN
    Actuals + Forecast

    Note: If you have proper relationships between FC_GL_TABLE[Forecast_ID] and FC_StartPeriodID_TABLE[Forecast_ID] in your model, you can even use RELATED instead of LOOKUPVALUE, which is a bit cleaner and faster.

    VAR SelectedStartDate = RELATED(FC_StartPeriodID_TABLE[FCStartDate])

     

    If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

    Thank you

5 Replies

  • v-dineshya's avatar
    v-dineshya
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi RasmusN532 ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    Create Measure with below DAX :

    Forecast_Amount_With_Actuals =
    VAR SelectedForecastID = SELECTEDVALUE(FC_GL_TABLE[Forecast_ID])
    VAR SelectedStartDate =
    LOOKUPVALUE(
    FC_StartPeriodID_TABLE[FCStartDate],
    FC_StartPeriodID_TABLE[Forecast_ID], SelectedForecastID
    )

    VAR Actuals =
    CALCULATE(
    SUM(FC_GL_TABLE[Amount]),
    FC_GL_TABLE[Forecast_Pa] = "GL",
    FC_GL_TABLE[Date] < SelectedStartDate,
    REMOVEFILTERS(FC_GL_TABLE[Forecast_ID])
    )

    VAR Forecast =
    CALCULATE(
    SUM(FC_GL_TABLE[Amount]),
    FC_GL_TABLE[Forecast_Pa] = "FC",
    FC_GL_TABLE[Forecast_ID] = SelectedForecastID
    )

    RETURN
    Actuals + Forecast

    Note: If you have proper relationships between FC_GL_TABLE[Forecast_ID] and FC_StartPeriodID_TABLE[Forecast_ID] in your model, you can even use RELATED instead of LOOKUPVALUE, which is a bit cleaner and faster.

    VAR SelectedStartDate = RELATED(FC_StartPeriodID_TABLE[FCStartDate])

     

    If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

    Thank you

    • v-dineshya's avatar
      v-dineshya
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi RasmusN532 ,

      As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
      If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

       

      Regards,

      Dinesh

      • RasmusN532's avatar
        RasmusN532
        Icon for Helper I rankHelper I
        1 year ago

        Hi!
        Thank you, I´ve found a workaround that we are currently using. I will try this as soon as I find the time. 

         

        Kind regards

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

    RasmusN532 , Try using

    dax
    Forecast_Amount =
    VAR SelectedForecastID = SELECTEDVALUE(FC_GL_TABLE[Forecast_ID])
    VAR SelectedFCStartPeriod = LOOKUPVALUE(FC_StartPeriod[FCStartDate], FC_StartPeriod[Forecast_ID], SelectedForecastID)

    VAR Forecast = CALCULATE(
    SUM(FC_GL_TABLE[Amount]),
    FC_GL_TABLE[Forecast_ID] = SelectedForecastID,
    FC_GL_TABLE[Forecast_Part] = "FC"
    )

    VAR Actuals = CALCULATE(
    SUM(FC_GL_TABLE[Amount]),
    FC_GL_TABLE[Forecast_Part] = "GL",
    FC_GL_TABLE[Date] < SelectedFCStartPeriod,
    ALLSELECTED(FC_GL_TABLE)
    )

    RETURN
    Actuals + Forecast