cancel
Showing results for
Did you mean:
Highlighted

Calculate weeks of stock based on future demand

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

Re: Calculate weeks of stock based on future demand

HI @Laurelatwork,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
4 REPLIES 4
Continued Contributor

Re: Calculate weeks of stock based on future demand

Can you post some sample data from your two tables?

Or if it's sensitive, create some dummy data?

Alex

Re: Calculate weeks of stock based on future demand

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
Community Support

Re: Calculate weeks of stock based on future demand

HI @Laurelatwork,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Microsoft

Re: Calculate weeks of stock based on future demand

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

Announcements

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors