Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
CRSB
Regular Visitor

how to get a rolling number forecast

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.

 

 WeekBeginning Inv.InboundOutboundEst. Ending Inv.
Current1                  1,000100250                        850
Future2                      850200300                        750
Future3                      750200100                        850
Future4                      8500100                        750
Future5                      7500100                        650
2 REPLIES 2
v-linhuizh-msft
Community Support
Community Support

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:

vlinhuizhmsft_0-1730098461124.png

 

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:

vlinhuizhmsft_1-1730098574563.png

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
Super User
Super User

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors