Use 1 dimension column for 2 measures in Matrix

[TK12345]

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

[lbendlin · Accepted solution]

see attached