Show in chart previous forecasts of the selected forecast in a slicer

[Anonymous]

Hello, I'm new to Power BI. I'm trying to replicate a dashboard that was done in another BI software. It's a line chart showing actual values of units sold (by month) and the forecast for the future (in the same chart). There is a column in the table called "version", which corresponds to the version of the forecasts that were created every month, so there are several 12-month forecasts in the table (advancing by one month). For example, version 2022001 is the January forecast that starts in January, and 2022006 is the June forecast.

The table with the forecast values looks like this:

VERSION	DATE	PREDICTIONS
2022001	2022-01	300
2022001	2022-02	340
2022001	2022-03	320
2022002	2022-02	310
2022002	2022-03	315
2022002	2022-04	325
2022003	2022-03	330

I started with a line chart and a slicer, but what happens is that the chart only shows the selected version of the forecast. I want the past versions to be shown on the previous months of the selected version of the forecast as well. For example, if I select the June version in the slider, the predictions start at June 2022, but I also want to see the previous forecasts corresponding to the previous months (the May forecast value for May, the April forecast value for April, until the first forecast).

This was achieved in the other software using a programming language, so I'm looking at doing something equivalent with DAX.

Maybe something like using the SELECTEDVALUE of the slicer and including previous versions of the forecasts in the months that are before the starting date of the selected forecast?

Thanks in advance!

[v-henryk-mstf]

Hi @Anonymous ,

Need to confirm with you that you want to see both the forecast data for the current date and the data for the month prior to the current date, based on the date selected? Can you provide a screenshot to describe this?

Looking forward to your reply.

Best Regards,
Henry

[Anonymous]

Hi Henry, thanks for replying.

Yes, I want to see the current forecast (i.e., the selected version in the slicer) and also see the previous months of previous forecast versions (not just one previous month). I have been trying creating measures with DAX and managed to advance a bit. 

Here is a screenshot. The selected forecast is the June one, and the months before should show the other versions of the forecast that correspond to their first month. So here May should show the first forecast of the May version (2022-05), April should show the first forecast of the April version, and so on. The idea is to see the history of the previous forecasts compared to the real values.

The selected version of forecast is June, so the forecast starts at June.

Best regards,

Andres

[Anonymous]

If I create a measure like this, it shows the selected version of the forecast properly:

Forecast =

CALCULATE(

SUM('Predictions'[Predictions]),

FILTER(

ALL('Predictions'[Forecast Version]),

'Predictions'[Forecast Version] = SELECTEDVALUE('Predictions'[Forecast Version])

)

)

What I want is to add to this measure the previous values of the selected forecast.

For example: If in the slicer I select Version 2022-06, it should show the forecast that starts in 2022-06, plus the previous forecasts.

The Version column corresponds to the first month of the Date for a specific Forecast. So Version 2022-06 means the first prediction corresponds in the Date column to 2022-06. Version 2022-05 starts in Date 2022-05, and so on.

In plain English it would be like: "Show the first month of the prediction (the MIN date) for each category of Version" IF it is before the selected date, otherwise show the selected forecast.

Regards