Hi all,
I need a little help with the following. I have a fact table with production and sales. 1 Key date that is linked with the calendar table. 1 column with the max sales date. This max Sales date needs to be compared with all the mutations before that date.
So the last date column will be used as column in the matrix. So I will have 3 colums, 05-06-2023, 03-07-2023 and 31-07-2023. What I would like to see is a measure with 2 variables. 1 Production and 1 Sales. For column 05-06-2023, I would like to the SUM of amounts where the Date Column <= 05-06-2023 and type = production, for the second column 03-07-2023 I would like to the SUM of amounts where the Date Column <= 03-07-2023 and type = production and so on. See expected outcome. The second variable needs to be the sales.
Who could help me out?
Expected Outcome:
| | 5-6-2023 | 3-7-2023 | 31-7-2023 |
| Production | 500 | 600 | 800 |
| Sales | 400 | 550 | 850 |
Data:
| Type | Date | Amount | LastDate | _KeyProduct |
| Production | 20-5-2023 | 400 | 5-6-2023 | 1a |
| Production | 30-5-2023 | 100 | 3-7-2023 | 1a |
| Production | 8-6-2023 | 100 | 3-7-2023 | 1a |
| Production | 5-7-2023 | 100 | 31-7-2023 | 1a |
| Production | 15-7-2023 | 100 | 31-7-2023 | 1a |
| Sales | 20-5-2023 | -250 | | 1a |
| Sales | 5-6-2023 | -150 | | 1a |
| Sales | 8-6-2023 | -150 | | 1a |
| Sales | 5-7-2023 | -150 | | 1a |
| Sales | 15-7-2023 | -150 | | 1a |
