Comparing forecasted value with actual, same month, 1, 2 & 3 months prior

[Top006]

Greetings,

I have a forecast running, forecasting a demand for the current month +3 months ahead.

Each month I save the data in order to compare this with the actual values. This is all put into a seperate PBI report together with the actual deliveries.

My issue is that I don't know how to create the measures needed to be able to compare eg. actual delivered for April '22 with the forecasted for April 3, 2, 1 & 0 month prior.

Tabel A: Forecasted volume (row headers indicate delivery date, column headers indicate when the data was saved)

		2022	2022	2022	2022	2022	2022	2022
		Jan	Feb	Mar	Apr	May	Jun	Jul
2022	Jan	84
2022	Feb	42	20
2022	Mar	36	57	11
2022	Apr	55	7	80	14
2022	May		85	15	27	94
2022	Jun			9	51	41	35
2022	Jul				44	37	97	62

Tabel B: What actually was supplied each month

		Actual
2022	Jan	93
2022	Feb	63
2022	Mar	21
2022	Apr	96
2022	May	38
2022	Jun	21
2022	Jul	53
2022	Aug	20

Expected output would be to find the forecasted volume for the same month and 1, 2, & 3 months prior as below:

		Same month	1 month prior	2 months prior	3 months prior
2022	Jan	84
2022	Feb	20	42
2022	Mar	11	57	36
2022	Apr	14	80	7	55
2022	May	94	27	15	85
2022	Jun	35	41	51	9
2022	Jul	62	97	37	44
2022	Aug	71	78	64	96

And finally ending up wiht the difference between the forecasted and the actual volume:

		Same month	1 month prior	2 months prior	3 months prior
2022	Jan	-9
2022	Feb	-43	-21
2022	Mar	-10	36	15
2022	Apr	-82	-16	-89	-41
2022	May	56	-11	-23	47
2022	Jun	14	20	30	-12
2022	Jul	9	44	-16	-9
2022	Aug	51	58	44	76

Table A and B is comined via a date table.

Any help is be much appreciated as I'm stuck.

Many thanks in advance

Thomas

[Top006]

Hi @v-jingzhang,

Sorry for the late respond on this.
Thank you very much for above suggestion and example - That did the trick.

Have a nice day ahead.

[v-jingzhang · Accepted solution]

Hi @Top006 

The current format of Table A is not friendlly to be used with DAX. The tables should be flat tables which means the first row is for column headers and the other rows are for records. So it would be better to have Table A in the following format. 

Table B:

Then create several measures to get the results you want. I also have a Date table in the model. You can download the attachment at bottom to see the formula of those measures. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.