How to get rolling actual data in My Matrix?

[Anonymous]

Hi All,

I have 2 data sets. The first one is my forecast and the second dataset has actuals.
Forecast Dataset
Report Date, Timeline date, Forecast Qty 

Actual Dataset

Report Date, Timeline Date, Actuals Qty
I want to create a waterfall matrix where I need to show all my Forecast Qty (Black font), the Actuals (Red fonts), and totals (Blue fonts)

The output should be as below table:

Report Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Total
Jan	100	200	300	50	250					900
Feb	90	20	250	100	400	300				1160
Mar	90	40	500	402	100	302	288			1722
Apr	90	40	400	700	650	300	100	500		2780
May	90	40	400	900	200	200	200	200	200	2430

Could anyone please help me in creating this output?

Thanks in advance.

[V-lianl-msft]

Hi @Anonymous ,

Maybe you can create measure to meet your needs, but it depends on your data model.

Can you paste the sample data and let me do more tests to see if I can solve your problem

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @V-lianl-msft 

The 2 data sets which I have explained above, Using that I have created a third table. Now I have forecast and actual qty in one column.  I just want my last column values to repeat till the end of the table. I am not able to repeat the last values. It will be really helpful if you can able to find any solution.

Report Date	Timeline Date	Source	Qty
1-Jan	1-Jan	Forecast Table	100
1-Jan	1-Feb	Forecast Table	200
1-Jan	1-Mar	Forecast Table	300
1-Jan	1-Apr	Forecast Table	50
1-Jan	1-May	Forecast Table	250
1-Feb	1-Feb	Forecast Table	20
1-Feb	1-Mar	Forecast Table	250
1-Feb	1-Apr	Forecast Table	100
1-Feb	1-May	Forecast Table	400
1-Feb	1-Jun	Forecast Table	300
1-Mar	1-Mar	Forecast Table	500
1-Mar	1-Apr	Forecast Table	402
1-Mar	1-May	Forecast Table	100
1-Mar	1-Jun	Forecast Table	302
1-Mar	1-Jul	Forecast Table	288
1-Apr	1-Apr	Forecast Table	700
1-Apr	1-May	Forecast Table	650
1-Apr	1-Jun	Forecast Table	300
1-Apr	1-Jul	Forecast Table	100
1-Apr	1-Aug	Forecast Table	500
1-May	1-May	Forecast Table	200
1-May	1-Jun	Forecast Table	200
1-May	1-Jul	Forecast Table	200
1-May	1-Aug	Forecast Table	200
1-Feb	1-Jan	Actual Table	90
1-Mar	1-Feb	Actual Table	40
1-Apr	1-Mar	Actual Table	400
1-May	1-Apr	Actual Table	900

Thanks in advance

AD