topic Re: how to get a rolling number forecast in DAX Commands and Tips

how to get a rolling number forecast

CRSB — Thu, 24 Oct 2024 21:27:13 GMT

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

Re: how to get a rolling number forecast

lbendlin — Thu, 24 Oct 2024 23:43:16 GMT

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

Re: how to get a rolling number forecast

Anonymous — Mon, 28 Oct 2024 07:00:40 GMT

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.