Actuals(Table) + Forecast(table) in one measure

[RasmusN532]

Hi, 

I am posting this again since it seems it didnt work the first time 🙂

Problem:

FC2501 starts in 202503->202603 so I want a measure that shows Actuals (GL) in 202501 and 202502 and then forecast in the remaining periods (as shown in below measure)

The measure below is somewhat working, because I get the correct amount in totals, and when I select all months. or march and onward. 

If I only select the actuals month (202501 and 202502) Its blank. But if I add 202503(202501->202503) I get the correct amount shown per account or category or whatever. What am I missing that is making this not work if I only select the actual months?

FC2501_U =

CALCULATE(

    [GL],

    '1.Calendar_Map'[PeriodValue] < 202503,'1.Calendar_Map'[Year] = 2025

)

+ CALCULATE([Forecast],Forecast[Forecast_ID]= "FC2501")

Thank you,

[v-csrikanth]

Hi @RasmusN532 

Thank you for being part of the Microsoft Fabric Community.

As highlighted by @FarhanJeelani , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[RasmusN532]

Hi again,

FC2501_U =

CALCULATE(

    [Utfall GL],

    '1.Calendar_Map'[PeriodValue] < 202503,'1.Calendar_Map'[Year] = 2025

)

+ CALCULATE([Forecast_DB_UTFALL],Forecast[Forecast_ID]= "FC2501")

Is working in all its glory. HOWEVER when its used in the switch measure, it completly forgets about the Forecast 2026 numbers and uses GL actuals for 2026 instead. So for this its actuals for 202501,202502 AND 202601 202602 202603.

See below switchfunction:

1FC_U_Switcher =
VAR SelectedForecast = SELECTEDVALUE(Forecast[Forecast_ID])
RETURN
IF(
ISBLANK(SelectedForecast),
SWITCH(TRUE(),
MAX('1.Calendar_Map'[PeriodValue]) < 202503, [FC2501_U],
MAX('1.Calendar_Map'[PeriodValue]) < 202506, [FC2502_U],
MAX('1.Calendar_Map'[PeriodValue]) < 202509, [FC2503_U],
MAX('1.Calendar_Map'[PeriodValue]) < 202512, [FC2504_U],
MAX('1.Calendar_Map'[PeriodValue]) < 202603, [FC2601_U],
MAX('1.Calendar_Map'[PeriodValue]) < 202606, [FC2602_U],
BLANK()
),
SWITCH(
SelectedForecast,
"BU25", [BU25_U],
"FC2501", [FC2501_U],
"FC2502", [FC2502_U],
"FC2503", [FC2503_U],
"FC2504", [FC2504_U],
"FC2601", [FC2601_U],
"FC2602", [FC2602_U],
BLANK()
)
)

Thanks in advance! 🙂

[FarhanJeelani · Accepted solution]

Hi @RasmusN532 ,

Your issue arises because when you filter only the Actual months (202501 and 202502), your CALCULATE([Forecast], Forecast[Forecast_ID]= "FC2501") still applies but doesn't return any values for those months, and thus the measure evaluates to blank.

You need to explicitly handle cases where only actual months are selected. Try using IF to check whether data exists for actuals before summing them.

Fixed Measure:

FC2501_U =
VAR Actuals =
CALCULATE(
[GL],
'1.Calendar_Map'[PeriodValue] < 202503,
'1.Calendar_Map'[Year] = 2025
)

VAR Forecasts =
CALCULATE(
[Forecast],
Forecast[Forecast_ID] = "FC2501"
)

RETURN
IF(NOT(ISBLANK(Actuals)), Actuals, 0) +
IF(NOT(ISBLANK(Forecasts)), Forecasts, 0)

If the issue persists, try visualizing both Actuals and Forecasts separately in a table to verify what each part of the measure returns for different selections.

Please mark this post as solution if it helps you. Appreciate Kudos.

[RasmusN532]

Thank you for the answer. Now it seems to work with the months 202501 and 202502. However, I am using this in a Switch measure:

1FC_U_Switcher =

VAR SelectedForecast = SELECTEDVALUE(Forecast[Forecast_ID])

RETURN

IF(

    ISBLANK(SelectedForecast),

    SWITCH(TRUE(),

        MAX('1.Calendar_Map'[PeriodValue]) < 202503, [FC2501_U],

        MAX('1.Calendar_Map'[PeriodValue]) < 202506, [FC2502_U],

        MAX('1.Calendar_Map'[PeriodValue]) < 202509, [FC2503_U],

        MAX('1.Calendar_Map'[PeriodValue]) < 202512, [FC2504_U],

        MAX('1.Calendar_Map'[PeriodValue]) < 202603, [FC2601_U],

        MAX('1.Calendar_Map'[PeriodValue]) < 202606, [FC2602_U],

        BLANK()

    ),

    SWITCH(

        SelectedForecast,

        "BU25", [BU25_U],

        "FC2501", [FC2501_U],

        "FC2502", [FC2502_U],

        "FC2503", [FC2503_U],

        "FC2504", [FC2504_U],

        "FC2601", [FC2601_U],

        "FC2602", [FC2602_U],

        BLANK()

    )

)
And for some reason.. the whole 2025 is correct but in 2026 it actually adds the actuals (like 7-10k each month, not many bookings next year yet, but will be a problem later on) and suddenly stops adding those from 202606 and onwards.  Se

I´ve seperated the actual measure as below:

Test_Actuals_2501 =

CALCULATE(

    [Utfall GL],

    '1.Calendar_Map'[PeriodValue] < 202503

)
and it works and only shows 202501 and 202502, so I am guessing something happens in the switchmeasure? 

I am completely lost here 😁