Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 2 tables: (1) Current Items & on-hand counts, (2) Forecasted weekly demand by item for the next year. Linked by item number.
I'm trying to understand how many weeks of stock (WOS) I have on-hand for each item today.
For example I have 100 units of Item A. The next 5 weeks of demand look like this:
Week 1: 20
Week 2: 12
Week 3: 40
Week 4: 55
Week 5: 30
In the 4th week, I run out of stock, so my WOS is 3 or 3.5 (I'm ok with either). How do I calculate this, by item? I'm comfortable with DAX but have a feeling Power Query is the answer here. Thanks!
Solved! Go to Solution.
HI @Anonymous,
If you mean calculate max available week of forecasted data, you can refer to below formula.
Calculate column at demand table:
Remain = VAR onHand = LOOKUPVALUE ( 'On-Hand'[OH Quantity], 'On-Hand'[Item Number], [Item Number], 'On-Hand'[Location], [Location], 'On-Hand'[Item Description], [Item Description] ) RETURN onHand - SUMX ( FILTER ( ALL ( Demand ), [Week Beginning] <= EARLIER ( Demand[Week Beginning] ) && [Item Number] = EARLIER ( Demand[Item Number] ) && [Item Description] = EARLIER ( Demand[Item Description] ) && [Location] = EARLIER ( Demand[Location] ) ), [Weekly Demand] )
Calculate column at On-hand table:
Forecasted available Week = VAR _date = MAXX ( FILTER ( ALL ( Demand ), Demand[Item Description] = 'On-Hand'[Item Description] && Demand[Item Number] = 'On-Hand'[Item Number] && Demand[Location] = 'On-Hand'[Location] && [Remain] >= 0 ), [Week Beginning] ) RETURN WEEKNUM ( _date, 1 ) & "/" & YEAR ( _date )
Result:
Regards,
Xiaoxin Sheng
@Anonymous possible for you to share the pbix file with measure accepted as solution below please? I am struggling with similar scenario.
Hi @Anonymous,
Can you post some sample data from your two tables?
Or if it's sensitive, create some dummy data?
Alex
On-hands:
Item Number | Item Description | Location | OH Quantity |
49928 | Item A | Loc 1 | 231 |
49928 | Item A | Loc 2 | 559 |
49928 | Item A | Loc 3 | 378 |
71594 | Item B | Loc 1 | 650 |
71594 | Item B | Loc 2 | 3562 |
71594 | Item B | Loc 3 | 693 |
73224 | Item C | Loc 2 | 770 |
73224 | Item C | Loc 3 | 317 |
Demand:
Item Number | Item Description | Location | Week Beginning | Weekly Demand |
49928 | Item A | Loc 1 | 6/3/2018 | 32 |
49928 | Item A | Loc 2 | 6/3/2018 | 213 |
49928 | Item A | Loc 3 | 6/3/2018 | 119 |
71594 | Item B | Loc 1 | 6/3/2018 | 126 |
71594 | Item B | Loc 2 | 6/3/2018 | 592 |
71594 | Item B | Loc 3 | 6/3/2018 | 102 |
73224 | Item C | Loc 2 | 6/3/2018 | 125 |
73224 | Item C | Loc 3 | 6/3/2018 | 67 |
49928 | Item A | Loc 1 | 6/10/2018 | 43 |
49928 | Item A | Loc 2 | 6/10/2018 | 53 |
49928 | Item A | Loc 3 | 6/10/2018 | 71 |
71594 | Item B | Loc 1 | 6/10/2018 | 158 |
71594 | Item B | Loc 2 | 6/10/2018 | 1210 |
71594 | Item B | Loc 3 | 6/10/2018 | 67 |
73224 | Item C | Loc 2 | 6/10/2018 | 201 |
73224 | Item C | Loc 3 | 6/10/2018 | 2 |
49928 | Item A | Loc 1 | 6/17/2018 | 30 |
49928 | Item A | Loc 2 | 6/17/2018 | 234 |
49928 | Item A | Loc 3 | 6/17/2018 | 201 |
71594 | Item B | Loc 1 | 6/17/2018 | 68 |
71594 | Item B | Loc 2 | 6/17/2018 | 730 |
71594 | Item B | Loc 3 | 6/17/2018 | 176 |
73224 | Item C | Loc 2 | 6/17/2018 | 212 |
73224 | Item C | Loc 3 | 6/17/2018 | 73 |
49928 | Item A | Loc 1 | 6/24/2018 | 104 |
49928 | Item A | Loc 2 | 6/24/2018 | 174 |
49928 | Item A | Loc 3 | 6/24/2018 | 175 |
71594 | Item B | Loc 1 | 6/24/2018 | 133 |
71594 | Item B | Loc 2 | 6/24/2018 | 750 |
71594 | Item B | Loc 3 | 6/24/2018 | 160 |
73224 | Item C | Loc 2 | 6/24/2018 | 168 |
73224 | Item C | Loc 3 | 6/24/2018 | 199 |
49928 | Item A | Loc 1 | 7/1/2018 | 64 |
49928 | Item A | Loc 2 | 7/1/2018 | 176 |
49928 | Item A | Loc 3 | 7/1/2018 | 184 |
71594 | Item B | Loc 1 | 7/1/2018 | 174 |
71594 | Item B | Loc 2 | 7/1/2018 | 480 |
71594 | Item B | Loc 3 | 7/1/2018 | 188 |
73224 | Item C | Loc 2 | 7/1/2018 | 124 |
73224 | Item C | Loc 3 | 7/1/2018 | 84 |
HI @Anonymous,
If you mean calculate max available week of forecasted data, you can refer to below formula.
Calculate column at demand table:
Remain = VAR onHand = LOOKUPVALUE ( 'On-Hand'[OH Quantity], 'On-Hand'[Item Number], [Item Number], 'On-Hand'[Location], [Location], 'On-Hand'[Item Description], [Item Description] ) RETURN onHand - SUMX ( FILTER ( ALL ( Demand ), [Week Beginning] <= EARLIER ( Demand[Week Beginning] ) && [Item Number] = EARLIER ( Demand[Item Number] ) && [Item Description] = EARLIER ( Demand[Item Description] ) && [Location] = EARLIER ( Demand[Location] ) ), [Weekly Demand] )
Calculate column at On-hand table:
Forecasted available Week = VAR _date = MAXX ( FILTER ( ALL ( Demand ), Demand[Item Description] = 'On-Hand'[Item Description] && Demand[Item Number] = 'On-Hand'[Item Number] && Demand[Location] = 'On-Hand'[Location] && [Remain] >= 0 ), [Week Beginning] ) RETURN WEEKNUM ( _date, 1 ) & "/" & YEAR ( _date )
Result:
Regards,
Xiaoxin Sheng
HI,
I have exactly similar requirement. The Solution provided here does not give the expected WOS - 3.5 or 3. It is returning the forecasted available date
Can someone help me by taking the same data as sample , but i will need a measure to calculate WOS based on selection of product
I have the same requirement, Do you have any solution so far? Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |