Combined Actuals & Forecast Volume Measure

[Blue_Carbon]

Hi, I've got 2 fact tables. 1 is the actuals which is linked to a calendar dimension table via the Calendar Date. Another is a forecast fact table which is linked to the calendar dimension table via a month end date. I then used a series of measures to allocate this forecast volume for each day.

The forecast data begins on 30/04/25 and so for prior months, I want it to sum the total actual borrowing amount.

I.e. if a user selected 01/03/25 I would want to see the actual borrowing amount for that day, or if they selected March 2025 I would want to see the actual borrowing amount for that month. Conversely, if they selected 01/04/25 I would want to see the the forecast volume for that day. 

The problem I am facing is that when I select say March 2025 (forecast data not present) and April 2025 (forecast data begins), my measure below is still calculating the sum of actuals + forecast. I believe this is because it isn't looking at the date correctly. Please can someone help?

New Forecast Volume =

VAR EarliestForecastDate = EDATE (

    MINX (Fact_Forecasts, Fact_Forecasts[Month End Date]),

- 1 )

VAR ForecastVolume = [Forecast Volume]

VAR ActualVolume = [Actual Borrowing Amount]

VAR PreForecast = CALCULATE (

    ActualVolume,

    Dim_Calendar[Calendar Date] <= EarliestForecastDate

)

VAR Result = CALCULATE (

    PreForecast + ForecastVolume

)

RETURN

Result

[Blue_Carbon · Accepted solution]

Hi everybody, thanks so much for your help. I've solved it now though, I went back and re-learnt how row and filter contexts work along with CALCULATE and FILTER. Turned out they were the only things I needed, so I've solved it now. For anyone who needs to use in the future:

Forecast Borrowing Amount =

VAR EarliestDate =

CALCULATE (

DATEVALUE ( EOMONTH ( MINX ( Fact_Forecasts, Fact_Forecasts[Month End Date]), -1 ) ),

ALL (Dim_Calendar)

)

VAR PreForecast =

CALCULATE (

[Actual Borrowing Amount],

FILTER (Dim_Calendar, Dim_Calendar[Calendar Date] <= EarliestDate)

)

VAR ForecastVolume = [Forecast Volume]

RETURN

CALCULATE (

ForecastVolume + PreForecast

)

[Blue_Carbon · Accepted solution]

Hi everybody, thanks so much for your help. I've solved it now though, I went back and re-learnt how row and filter contexts work along with CALCULATE and FILTER. Turned out they were the only things I needed, so I've solved it now. For anyone who needs to use in the future:

Forecast Borrowing Amount =

VAR EarliestDate =

CALCULATE (

DATEVALUE ( EOMONTH ( MINX ( Fact_Forecasts, Fact_Forecasts[Month End Date]), -1 ) ),

ALL (Dim_Calendar)

)

VAR PreForecast =

CALCULATE (

[Actual Borrowing Amount],

FILTER (Dim_Calendar, Dim_Calendar[Calendar Date] <= EarliestDate)

)

VAR ForecastVolume = [Forecast Volume]

RETURN

CALCULATE (

ForecastVolume + PreForecast

)

[gmsamborn · Accepted solution]

Hi @Blue_Carbon 

I was looking at the code you said works for you.

2 points regarding your code:

1 - Your use of CALCULATE in the RETURN does nothing.
Variables are set when they are created and never change. (They should actually be called constants.)
Wrapping the addition of 2 variables with CALCULATE won't change the result regardless of any filters you might use to change the filter context.

2 - Your use of DATEVALUE is also extraneous - and also surprising that it works for you.
DATEVALUE expects a string argument that represents a date whereas you are using an actual date so DAX must be doing a datatype conversion in the background.
The usage with a date argument isn't mentioned in the documentation.

Can you check to see if this works for you?

Forecast Borrowing Amount (mine) =
VAR EarliestDate =
    CALCULATE(
        EOMONTH(
            MINX(
                Fact_Forecasts,
                Fact_Forecasts[Month End Date]
            ),
            -1
        ),
        ALL( Dim_Calendar )
    )
VAR PreForecast =
    CALCULATE(
        [Actual Borrowing Amount],
        FILTER(
            Dim_Calendar,
            Dim_Calendar[Calendar Date] <= EarliestDate
        )
    )
VAR ForecastVolume = [Forecast Volume (orig)]
RETURN
    ForecastVolume + PreForecast

I hope this makes sense.  Let me know if you have any questions.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.  Please also show the expected result in a simple Table format.

[V-yubandi-msft]

Hi @Blue_Carbon ,

Thank you for reaching out to the Microsoft Fabric Community. I’ve checked the scenario, and the results are now as expected.

1. March 2025 (no forecast)- displays actuals only → 50,000
2. April 2025 (forecast starts) - displays forecasts only → 100,000
3. Both months selected- displays actuals + forecasts → 150,000

FYI:

The logic automatically adjusts based on the selected period, keeping actuals and forecasts separate with no overlap.

I’ve attached the .pbix file for your reference. Please review it and let me know if you need any changes.

Warm regards,

Yugandhar.

[Blue_Carbon]

Thanks for the responses everybody, unfortunately none of them have worked. @MattiaFratello I thought your suggestion might be closest, but it's not bringing in the actuals when no forecast data is present and it seem to be totally all of my forecast data at a higher granularity too

[Shahid12523]

Your measure is double-counting. Use a switch-over logic

New Forecast Volume =
VAR FirstForecastDate =
MINX ( Fact_Forecasts, Fact_Forecasts[Month End Date] )
RETURN
SWITCH (
TRUE(),
MAX ( Dim_Calendar[Calendar Date] ) < FirstForecastDate, [Actual Borrowing Amount],
MAX ( Dim_Calendar[Calendar Date] ) >= FirstForecastDate, [Forecast Volume]
)

[MattiaFratello]

New Forecast Volume =
VAR EarliestForecastDate = MINX(Fact_Forecasts, Fact_Forecasts[Month End Date])
VAR SelectedMinDate = MIN(Dim_Calendar[Calendar Date])
VAR SelectedMaxDate = MAX(Dim_Calendar[Calendar Date])

VAR IsAllBeforeForecast = SelectedMaxDate < EarliestForecastDate
VAR IsAllAfterOrOnForecast = SelectedMinDate >= EarliestForecastDate

RETURN
SWITCH(
TRUE(),
IsAllBeforeForecast,
[Actual Borrowing Amount],
IsAllAfterOrOnForecast,
[Forecast Volume],
/* If the selection spans before and after the forecast start date, sum actuals for dates before and forecast for dates on/after */
CALCULATE(
[Actual Borrowing Amount],
Dim_Calendar[Calendar Date] < EarliestForecastDate
) +
CALCULATE(
[Forecast Volume],
Dim_Calendar[Calendar Date] >= EarliestForecastDate
)
)

[MattiaFratello]

Hi @Blue_Carbon, could you please post an example?

[Blue_Carbon]

Hey, yes, so for example, if I select June 2025 in the report filters, I would expect to see just the forecast volume for June 2025, but instead I get this (measure in question is New Forecast Volume):

Forecast Volume	Actual Borrowing Amount	New Forecast Volume
100,000	50,000	150,000