Fill the blanks with last month's figure

[lucasneedhelp]

Hi ALL,

I've been stuck here for 3 days and please help. Really appreciated!

This is my original sample data, and the final goal is to have a table of forecast amount by Endofmonth (Forecast Date) and by Dept Group. when there's a amount for the Dept Group in a particular month, use the amount. otherwise use last nonblankvalue. Dept Group add up all Res Code changes within the depart group

Event ID  Forecast Date Amount    Dept Group              Res Code

83221	19/11/2021	34800	Venue Hire	MECSPACERENT
83221	9/06/2022	0	Venue Hire	MCCSPACERENT
83221	9/06/2022	50940	Venue Hire	MECSPACERENT
83221	5/12/2022	33960	Venue Hire	MECSPACERENT
83221	13/01/2023	300	Event Service	EVSVELECFORE
83221	13/01/2023	582.56	Event Service	EVSVECRFC
83221	13/01/2023	711.7	Event Service	EVSVBAYFORE
83221	13/01/2023	70000	Catering	FBFORECASTS
83221	16/01/2023	9677	TSD	AVFORECAST
83221	1/02/2023	4770	Venue Hire	MCCSPACERENT
83221	21/02/2023	10472	TSD	AVFORECAST
83221	4/05/2023	8697.36	TSD	AVFORECAST
83221	4/05/2023	8687.36	TSD	AVFORECAST
83221	4/05/2023	8022.36	TSD	AVFORECAST
83221	22/05/2023	8867.36	TSD	AVFORECAST

And here's my desired results. it needs to list every month between first forecast month and last forecast month. 

Month Year	Catering	Event Service	TSD	Venue Hire
Nov-2021				34800
Dec-2021				34800
Jan-2022				34800
Feb-2022				34800
Mar-2022				34800
Apr-2022				34800
May-2022				34800
Jun-2022				50940
Jul-2022				50940
Aug-2022				50940
Sep-2022				50940
Oct-2022				50940
Nov-2022				50940
Dec-2022				33960
Jan-2023	70000	1594.26	9677	33960
Feb-2023	70000	1594.26	10472	38730
Mar-2023	70000	1594.26	10472	38730
Apr-2023	70000	1594.26	10472	38730
May-2023	70000	1594.26	8867.36	38730

Could someone please help with Dax? Thank you so very much!

[lbendlin · Accepted solution]

See attached

[lucasneedhelp]

thanks heaps for your prompt assistance!!!

although it's a bit out from desired results.

the forecast change is attached to each resouce code.

There are two forecast change scenarios:
1. when same resouce changes, i'd like the result to replace last number. ie.) row 4 in the data sample
2.when a new resouce appears, i'd like the result to add new resouce forecast. ie.) row 10 in the data sample
therefore the final venue hire forecast is 38,730 (33,960+4,770)
the goal of this analyisis is to track forecast for each department at any point of time also to track final forecast amount

do you think it's possible? or are there too many variables?

[lbendlin]

I don't know what you refer to as row 4 or row 10.

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[lucasneedhelp]

sample data remains the same

see expected results as below 

Month Year	Catering	Event Service	TSD	Venue Hire
Nov-21				34,800
Dec-21				34,800
Jan-22				34,800
Feb-22				34,800
Mar-22				34,800
Apr-22				34,800
May-22				34,800
Jun-22				50,940
Jul-22				50,940
Aug-22				50,940
Sep-22				50,940
Oct-22				50,940
Nov-22				50,940
Dec-22				33,960
Jan-23	70,000	1,594	9,677	33,960
Feb-23	70,000	1,594	10,472	38,730
Mar-23	70,000	1,594	10,472	38,730
Apr-23	70,000	1,594	10,472	38,730
May-23	70,000	1,594	8,867	38,730

[lbendlin]

I'm sorry, that is too ambiguous for me. I also don't understand your expected result for May 2023.  It seems to me like you are mixing different concepts.

Maybe someone else can help you.  

[lucasneedhelp]

Thank you for your help so far.

maybe it's too hard to achieve in Power BI.