Hi,
we do Forecasts based on two factors:
1. Comparison Date, e.g. we compare 2021/2/28 to 2020/3/1
2. Target-Factor, e.g. 1.1 (that means we want to have 110% of the revenue of the Comparison Date)
I want to do this with two tables in my Data Model:
1) Facts Table (Random Data)
| Location | Date | ArticleId | Revenue | Location-Date |
| A | 2021/2/28 | 12312312312 | 50.19 | A-2021/2/28 |
| A | 2021/2/15 | 123123451 | 100.83 | A-2021/2/15 |
| B | 2021/1/31 | 23161 | 200.21 | B-2021/1/31 |
| C | 2021/4/16 | 213613513512 | 25.23 | C-2021/4/16 |
| A | 2020/3/1 | 12312312312 | 100 | A-2020/3/1 |
| A | 2020/2/17 | 123123451 | 200 | A-2020/2/17 |
| B | 2020/1/28 | 23161 | 100 | B-2020/1/28 |
| C | 2020/4/18 | 213613513512 | 50 | C-2020/4/18 |
2) Comparison Dates and Factors (Random Data)
| Location-Date | Comparison Date | Target Factor |
| A-2021/2/28 | 2020/3/1 | 1.1 |
| A-2021/2/15 | 2020/2/17 | 1.2 |
| B-2021/1/31 | 2020/1/28 | 1.3 |
| C-2021/4/16 | 2020/4/18 | 1.1 |
The Result should look like (with the option to add ArticleNames or CommodityGroups as dimensions):
| Date | Location | Revenue | Target Revenue |
| 2021/2/28 | A | 50.19 | 110 |
| 2021/2/15 | A | 100.83 | 240 |
| 2021/1/31 | B | 200.21 | 130 |
| 2021/4/16 | C | 25.23 | 55 |
Questions:
1) How can i do this with set analysis?
2) I think i get troubles with the ArticleId. On the one hand i want to compare commodity groups or even articles, and on the other hand i dont want to exclude articles if they were not sold at the date, but only at the comparison date.
Thanks alot!
