How to incorporate Forecast LAG into data model

joshua1990 — Sun, 26 Mar 2023 12:21:10 GMT

Hi experts!

I am working on a MAPE Report for our Forecast Accurary Report.

In this report we have 4 tables:

Actuals for the past

Then I have a fiscal calendar table and a master data table that helps me to aggregate this data for specific months and article groups.

Then I have a forecasting table like this:

Now I would like to add a filter for the LAG into the report based on this logic:

If I select 0, I would like to see the actuals for the individual month vs the forecast for that same month based on previous cycle numbers
- For instance
  - We are in March 2023
  - Then I would like to see the actuals for Feb 2023 vs the forecast of 2023-02
  - I would like to see the actuals for Jan 2023 vs the forecast of 2023-01
If I select 1 then I would like to the forecast 2 cycles before.

How would you build that kind of logic into the model?

topic How to incorporate Forecast LAG into data model in DAX Commands and Tips