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.
