cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Laurelatwork Advocate I
Advocate I

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
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:

25.PNG26.PNG

 

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

View solution in original post

4 REPLIES 4
alexei7 Continued Contributor
Continued Contributor

Re: Calculate weeks of stock based on future demand

Hi @Laurelatwork,

 

Can you post some sample data from your two tables?

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

 

Alex

 

 

Highlighted
Laurelatwork Advocate I
Advocate I

Re: Calculate weeks of stock based on future demand

On-hands: 

Item NumberItem DescriptionLocationOH Quantity
49928Item ALoc 1231
49928Item ALoc 2559
49928Item ALoc 3378
71594Item BLoc 1650
71594Item BLoc 23562
71594Item BLoc 3693
73224Item CLoc 2770
73224Item CLoc 3317


Demand:

Item NumberItem DescriptionLocationWeek BeginningWeekly Demand
49928Item ALoc 16/3/201832
49928Item ALoc 26/3/2018213
49928Item ALoc 36/3/2018119
71594Item BLoc 16/3/2018126
71594Item BLoc 26/3/2018592
71594Item BLoc 36/3/2018102
73224Item CLoc 26/3/2018125
73224Item CLoc 36/3/201867
49928Item ALoc 16/10/201843
49928Item ALoc 26/10/201853
49928Item ALoc 36/10/201871
71594Item BLoc 16/10/2018158
71594Item BLoc 26/10/20181210
71594Item BLoc 36/10/201867
73224Item CLoc 26/10/2018201
73224Item CLoc 36/10/20182
49928Item ALoc 16/17/201830
49928Item ALoc 26/17/2018234
49928Item ALoc 36/17/2018201
71594Item BLoc 16/17/201868
71594Item BLoc 26/17/2018730
71594Item BLoc 36/17/2018176
73224Item CLoc 26/17/2018212
73224Item CLoc 36/17/201873
49928Item ALoc 16/24/2018104
49928Item ALoc 26/24/2018174
49928Item ALoc 36/24/2018175
71594Item BLoc 16/24/2018133
71594Item BLoc 26/24/2018750
71594Item BLoc 36/24/2018160
73224Item CLoc 26/24/2018168
73224Item CLoc 36/24/2018199
49928Item ALoc 17/1/201864
49928Item ALoc 27/1/2018176
49928Item ALoc 37/1/2018184
71594Item BLoc 17/1/2018174
71594Item BLoc 27/1/2018480
71594Item BLoc 37/1/2018188
73224Item CLoc 27/1/2018124
73224Item CLoc 37/1/201884
Community Support
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:

25.PNG26.PNG

 

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

View solution in original post

Microsoft
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

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors