Dynamic Actuals with Forecast

[RasmusN532]

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

[v-dineshya · Accepted solution]

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]

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]

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

[v-dineshya]

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

[bhanu_gautam]

@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