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
| Date |
Value |
Article |
| DD.MM.YYYY |
5 |
A |
| DD.MM.YYYY |
6 |
B |
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:
| Cycle (Year-Month) |
Date |
Value |
Article |
| 2021-01 |
DD.MM.YYYY |
5 |
A |
| 2021-01 |
DD.MM.YYYY |
6 |
B |
| 2021-02 |
DD.MM.YYYY |
7 |
A |
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?
- Any new kind of table?
- What kind of measures?
