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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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:

25.PNG26.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
mb0307
Responsive Resident
Responsive Resident

@Anonymous  possible for you to share the pbix file with measure accepted as solution below please?  I am struggling with similar scenario.

alexei7
Continued Contributor
Continued Contributor

Hi @Anonymous,

 

Can you post some sample data from your two tables?

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

 

Alex

 

 

Anonymous
Not applicable

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

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:

25.PNG26.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Anonymous
Not applicable

I have the same requirement, Do you have any solution so far? Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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