Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 |
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.
Looks like you have already solved the issue? What exactly do you need help with?
User | Count |
---|---|
21 | |
20 | |
12 | |
10 | |
8 |
User | Count |
---|---|
30 | |
28 | |
14 | |
13 | |
11 |