Combining Actuals and Forecast, depending on Forecast Version

[larbalette]

Hello everyone,
I'm struggling with an issue that seems simple to solve, but I cannot make it. I've tried many ideas, with no success.
I have 2 tables :
- Actuals (RealiséCA), with combinations of Customer/Article/Month data, and for each combination, 2 lines for 2 key figures, Quantity and Turnover. And of course a last column with the value of the key figure for the specifief Customer/Article/Month of sales.

customer	article	month	Jour	KeyFigure	Valeur
A	GD16	202401	01/01/2024 00:00:00	quantité	124
A	GD16	202402	01/02/2024 00:00:00	quantité	100

- Forecast (ForecastCA), with combinations of Customer/Article/Month data, and for each combination, 2 key figures as well (Quantity and Turnover) but I have as well a Version column, with the Forecast version ([Version (semaine)] column) (therefore, multiplying the number of lines since each combination/keyfigure is existing for each version).

customer	article	month	Jour	KeyFigure	Version (semaine)	Valeur
A	GD16	202405	01/05/2024 00:00:00	quantité	2024-01	125
A	GD16	202405	01/05/2024 00:00:00	quantité	2024-05	132
A	GD16	202402	01/02/2024	quantité	2024-01	102

I've built a third table, Pilotage, that is a combination of Actuals and Forecast on the 3 dimensions Customer/Article/Month, with no duplicates.

customer

article

month

Jour

Why ? Because I may have actuals on customer/article that I don't have in forecast (old articles that have been stopped), and I may have as well Forecast on customer/article that will be launched soon (and are still not in Actuals)

I've created relationships "one to many" between a key Customer/Article/Month between Pilotage table and the 2 others (Forecast and Actuals)
Data is at a monthly level (even if I translate months into dates, the [Jour] column below, the first of each month of sales for calculation purpose)
I've added a segment on Forecast Version, to be able to select a Forecast Version and apply this selection as a part of the context filter.

As you can undertand, I can have overlaps between Actuals and Forecast depending on the selected Forecast Version.
Imagine I select forecast version 2024-01 (version of forecast done the First week of 2024), and we are currently mid of March 2024, I got Actuals for Jan/Feb and the first days of March, but I have as well forecast for Jan/Feb and March, since the 2024-01 forecast version was done begginning of Jan 2024.

My issue is that I want to build a measure that, depending on the filter context, will aggregate Actuals + Forecast, with no overlap depending on the forecast version.
If forecast data exists, I dont't want to sum up Actuals. If forecast data doesn't exists, then actuals have to be summed up.
I want this to work depending on the contect filter (customer, article, month of sales selection)

example with above data : for the context of customer A, article GD16 and month 202402, forecast version select = 2024-01, I want 102 to be included in the calculation, and not 100 (the actual)

The big concern I'm facing is that Forecast Version ([Version (semaine)] column) is not present in Actuals table, and seems to be an issue (looks like it's filtering out the actuals whatever I'm trying to do)

I've tried this measure, first for the Quantity key figure :

PilotageQty = 
VAR FirstMonthForecast = CALCULATE(
MIN('ForecastCA'[Jour]),
ALL(ForecastCA),
'ForecastCA'[Version (semaine)] = SELECTEDVALUE(ForecastCA[Version (semaine)])
)
VAR Forecast = CALCULATE(Sum(ForecastCA[Valeur]), ForecastCA[KeyFigure] = "quantité")
VAR Actual = CALCULATE(Sum('RealiséCA'[Valeur]),
'RealiséCA'[KeyFigure] = "quantité" && 
'RealiséCA'[Jour] < FirstMonthForecast)
RETURN
Actual + Forecast

In a word, I get the soonest date within Forecast data (unfiltered with ALL, but filtered with the Forecast Version selected by the dedicated segment)
Forecast variable is correctly calculated.
But Actual is not (or maybe it is, but I cannot see it).
When I display a simple TABLE card with Pilotage table keys displayed , as soon as the Customer/Article/Month is in the past (compared to Forecast Version) and I'm supposed to get Actuals, the combination Customer/Article/Month is filtered out and not even displayed.
I'm not even able to understand whether it is the measure that is wrong, and merely the context filter applied on my card and filtering out data ?
Nevertheless, when I remove the PilotageQty measure from the table, past data are back. I guess the measure is wrong...

Could anyone help me on this ?
(I'm quite new on PowerBI)
Thanks !

[larbalette · Accepted solution]

I think I've found the solution : I had to manage the case when FirstMonthOfForecast was not found (BLANK)
This measure is working well :

PilotageQty =
VAR FirstMonthForecast = CALCULATE(
        MIN('ForecastCA'[Jour]),
        ALL(ForecastCA),
        'ForecastCA'[Version (week)] = SELECTEDVALUE(ForecastCA[Version (week)])
    )
VAR Forecast = CALCULATE(Sum(ForecastCA[Value]), ForecastCA[KeyFigure] = "quantité")
VAR Actual = CALCULATE(Sum('RealiséCA'[Value]), 'RealiséCA'[KeyFigure] = "quantité")
VAR qty = if(ISBLANK(FirstMonthForecast), Actual, Forecast)
RETURN
qty

Here is the result : with 2024-11 version selected

with 2024-02 version selected 

[larbalette]

Hi @Anonymous 

Thank you very much for your answer and help.

I've tried your proposal, but it's indeed not doing what I'm looking for.

I see that it's better to post images for better understanding, so let's do that and let's try to be clearer 😉
My model is built that way :

where Pilotage table, contains a combination of Forecast and Actuals (RealiséCA) table on their keys (Customer/Article/Month of sales). Keys is the "Clé" column. Pilotage has no duplicates, but some keys are present in Actuals but not in Forecast, some keys are in Forecast, not in Actuals, some keys are in both tables.
Forecast has a specificity : it has a "version" column (whose content is a YYYY-WW formatted string showing the week when the forecast has been done)

what I'm trying to do is the following :

when I select for example Forecast version 2024-02 (second week of 2024, in January), it means that I have forecast from January onwards. You can follow the green circles and arrows. Since I have forecast, I don't want the measure to take actuals on these Month of Sales => Actuals should only be taken on Month of sales where there is NO Forecast (Red rectangles)
At the end, for a Customer/Article as shown on the image, I want each month of sales beeing calculated whether by Actual or by Forecast depending on the selected Forecast version.

And of course, I want this measure to keep consistency when I will agregate data.
Second example : 

If I select 2024-11 forecast version (week 11, that is to say in March), you can see that I have no forecast for 202401 and 202402 month of sales => forecast is starting in March (202403) (green circles and arrows). As a consequence, I'm expecting the measure to take Actuals for months of sales before 202403 (202403 excluded) (Red rectangles) and forecast beyond (blue rectangles).

Ultimately, what I want to achieve (second difficulty for me ! 😁) is that kind of reporting :

just for you to understand the goal.
If you have any idea, I would greatly appreciate it !

Thanks,

[Anonymous]

Hi @larbalette

I'm not sure what result you want, but I made a change to your code.

Here's some dummy data

“RealiséCA”

“ForecastCA”

Create a measure. The code changes are as follows:

PilotageQty = 
VAR SelectedVersion = SELECTEDVALUE(ForecastCA[Version (semaine)])
VAR FirstMonthForecast = CALCULATE(
    MIN(ForecastCA[Jour]),
    ALL(ForecastCA),
    ForecastCA[Version (semaine)] = SelectedVersion
)
VAR Forecast = CALCULATE(
    SUM(ForecastCA[Valeur]),
    ForecastCA[KeyFigure] = "quantité",
    ForecastCA[Version (semaine)] = SelectedVersion
)
VAR Actual = CALCULATE(
    SUM('RealiséCA'[Valeur]),
    'RealiséCA'[KeyFigure] = "quantité",
    NOT('RealiséCA'[Jour]>= FirstMonthForecast)
)

RETURN
    Actual + Forecast

Here is the result.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.