how to get a rolling number forecast

[CRSB]

I am working to create a visual like the below for a forecasting tool. i have current inventory to use as a starting place, but for future weeks, I want to use the ending week value from the prior week. I struggle past the future weeks, when an actual beginning inventory value does not exist. 

My est ending inv = beginning + inbound - outbound

i want my prior week est ending inv to serve as my beginning inventory for the next week, example i want beginning inv for week two to be my ending inventory for week 1. My beginning for week 1 my only actual starting value.

	Week	Beginning Inv.	Inbound	Outbound	Est. Ending Inv.
Current	1	1,000	100	250	850
Future	2	850	200	300	750
Future	3	750	200	100	850
Future	4	850	0	100	750
Future	5	750	0	100	650

[Anonymous]

Thanks for the reply from lbendlin.

Hi @CRSB ,

Based on your description, I created simple data and achieved the desired result, here are my steps:

1. Create four new tables and create the following relationships:

2.Create two measures:

Est. Ending Inv. = 
 VAR _week=MAX('Week'[Week])
 VAR _Inbound=SUMX(FILTER(ALL('Inbound'),'Inbound'[Week]<=_week),'Inbound'[Inbound])
 VAR _Outbound=SUMX(FILTER(ALL('Outbound'),'Outbound'[Week]<=_week),'Outbound'[Outbound])
 RETURN
 SUMX(ALL('Beginning Inv'),'Beginning Inv'[Beginning Inv.])+_Inbound-_Outbound

Week Beginning Inv. = 
VAR CurrentWeek = SELECTEDVALUE('Week'[Week])
RETURN
IF(CurrentWeek=1, MAX('Beginning Inv'[Beginning Inv.]),CALCULATE([Est. Ending Inv.], FILTER(ALL('Week'), 'Week'[Week] = CurrentWeek - 1)))

3.The result is as follows:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[lbendlin]

Looks like you have already solved the issue?  What exactly do you need help with?