cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
manoranjan Frequent Visitor
Frequent Visitor

Stock Cover Calculation Measure

Hi All,

 

I have Weekly data of Stock and Planned Sales. For any given week, I want to Calculate the Stock Cover.

Stock Cover = How many weeks of Sales I can cover with the Current Stock.

 

My Data is like this

Week

Stock

Sales Plan

W01

100

40

W02

120

40

W03

130

40

W04

110

40

W05

80

40

W06

100

50

W07

120

50

 

 

In the above example, Week 1 Stock = 100 units and with that I can cover my sales for next 2.5 weeks. ( In other words I can sell w2, w3, and 0.5 of w4).

 

I want to build a measure that can show below table

Week

Stock

Sales Plan

Stock Cover

W01

100

40

2.5

W02

120

40

3.0

W03

130

40

3.2

W04

110

40

2.6

 

Please help, how to calculate the Stock Cover Measure.

 

Thanks in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Stock Cover Calculation Measure

@manoranjan,

 

You may check the following DAX.

Column =
VAR s = Table1[Stock]
VAR w = Table1[Week]
VAR t =
    FILTER ( Table1, Table1[Week] > w )
VAR t2 =
    ADDCOLUMNS (
        t,
        "total", SUMX (
            FILTER ( t, Table1[Week] <= EARLIER ( Table1[Week] ) ),
            Table1[Sales Plan]
        )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( t2, [total] >= s ) )
            > 0,
        COUNTROWS ( FILTER ( t2, [total] < s ) )
            + DIVIDE (
                s
                    - MAXX ( TOPN ( 1, FILTER ( t2, [total] < s ), Table1[Week], DESC ), [total] ),
                MAXX (
                    TOPN ( 1, FILTER ( t2, [total] >= s ), Table1[Week], ASC ),
                    Table1[Sales Plan]
                )
            )
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 REPLIES 9
manoranjan Frequent Visitor
Frequent Visitor

Iterative / Recursive Calculation

Please guide me how to solve this.

 

I have weekly Data of "Planned Stock" and "Planned Sales". I should calculate what is my Stock Cover every week. 

Stock Cover is described as "How many weeks forward of Sales I can cover with the current week Stock".

 

For example

Week

Stock

Sales Plan

Stock Cover

W01

100

40

2.5

W02

120

40

3.0

W03

130

40

3.2

W04

110

40

3.0

W05

80

40

 2.8

W0610050 3.0

 

First week I have 100 Units in Stock, with which I can cover the sales for next 2.5 weeks.

I want to write a Measure to calculate this on the fly, as there are other dimensions in the Data like Product Type, Category, Price Range etc. which I ignored in this example data for the sake of simplicity.

 

Thanks in advance.

venug20 Member
Member

Re: Stock Cover Calculation Measure

@manoranjan

 

use below formula to achive..

 

Stock Cover = SUM(Weeks[Stock]) / SUM(Weeks[Sales Plan])

 

Stock-Cover.jpg

 

If it is solution to your query, Pls accept as solution...

manoranjan Frequent Visitor
Frequent Visitor

Re: Stock Cover Calculation Measure

Thanks for reply @venug20

 

But what I want to achieve is different.

For example, my current week (week 1) Stock is 100 units.

Sales plan

W2 = 40,

W3=50,

W4=50

 

With the current stock of 100 units, I can sell upto 2.1 weeks forward.

 

Your solution takes care of only the Current Weeks sales. So this will not work in my scenario.

 

Community Support Team
Community Support Team

Re: Stock Cover Calculation Measure

@manoranjan,

 

You may check the following DAX.

Column =
VAR s = Table1[Stock]
VAR w = Table1[Week]
VAR t =
    FILTER ( Table1, Table1[Week] > w )
VAR t2 =
    ADDCOLUMNS (
        t,
        "total", SUMX (
            FILTER ( t, Table1[Week] <= EARLIER ( Table1[Week] ) ),
            Table1[Sales Plan]
        )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( t2, [total] >= s ) )
            > 0,
        COUNTROWS ( FILTER ( t2, [total] < s ) )
            + DIVIDE (
                s
                    - MAXX ( TOPN ( 1, FILTER ( t2, [total] < s ), Table1[Week], DESC ), [total] ),
                MAXX (
                    TOPN ( 1, FILTER ( t2, [total] >= s ), Table1[Week], ASC ),
                    Table1[Sales Plan]
                )
            )
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
manoranjan Frequent Visitor
Frequent Visitor

Re: Stock Cover Calculation Measure

Thank you @v-chuncz-msft Smiley Very Happy

 

This solves the problem I mentioned in my post. 

I should say, you are genius.

 

Regards

Mano

manoranjan Frequent Visitor
Frequent Visitor

Re: Stock Cover Calculation Measure

@v-chuncz-msft

 

I tried implementing this in my production Application. I was expecting this Stock Cover calculation to happen on-the-fly based on my other selections like Product Category, Store Type, Territory etc.

 

But when we add a column it calculates the Stock Cover as a static column to the Table. This is a problem in this case, as the Sum(Stock) and Sum(Sales) varies depending on the filters made in the Slicers.

 

Any workaround you can suggest? Please help.

 

Regards

Mano

 

darylmc Frequent Visitor
Frequent Visitor

Re: Stock Cover Calculation Measure


@v-chuncz-msft wrote:

@manoranjan,

 

You may check the following DAX.

Column =
VAR s = Table1[Stock]
VAR w = Table1[Week]
VAR t =
    FILTER ( Table1, Table1[Week] > w )
VAR t2 =
    ADDCOLUMNS (
        t,
        "total", SUMX (
            FILTER ( t, Table1[Week] <= EARLIER ( Table1[Week] ) ),
            Table1[Sales Plan]
        )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( t2, [total] >= s ) )
            > 0,
        COUNTROWS ( FILTER ( t2, [total] < s ) )
            + DIVIDE (
                s
                    - MAXX ( TOPN ( 1, FILTER ( t2, [total] < s ), Table1[Week], DESC ), [total] ),
                MAXX (
                    TOPN ( 1, FILTER ( t2, [total] >= s ), Table1[Week], ASC ),
                    Table1[Sales Plan]
                )
            )
    )

 

Hi All,

 

the above DAX is perfect for a simple table. Can it be further adapted to take into account additional dimensions as per below table?

 

Any help appreciated

 

Product

Week

StockSales PlanStock Cover
AW0110040 
AW0212040 
AW0313040 
AW0411040 
AW058040 
AW0610050 
AW0712050 
BW0115080 
BW0218080 
BW0319580 
BW0416580 
BW0512080 
BW06150100 
BW07180100 
darylmc Frequent Visitor
Frequent Visitor

Re: Stock Cover Calculation Measure

Got it, in the end. Man Happy

 

Column = 
VAR s = Table1[Stock]
VAR w = Table1[Week]
VAR x = Table1[Product]
VAR t =
    FILTER ( Table1, Table1[Week] > w && Table1[Product] = x)
VAR t2 =
    ADDCOLUMNS (
        t,
        "total", SUMX (
            FILTER ( t, Table1[Week] <= EARLIER ( Table1[Week] )&&Table1[Product]=EARLIER(Table1[Product])),
            Table1[Sales Plan]
        )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( t2, [total] >= s ) )
            > 0,
        COUNTROWS ( FILTER ( t2, [total] < s ) )
            + DIVIDE (
                s
                    - MAXX ( TOPN ( 1, FILTER ( t2, [total] < s ), Table1[Week], DESC ), [total] ),
                MAXX (
                    TOPN ( 1, FILTER ( t2, [total] >= s ), Table1[Week], ASC ),
                    Table1[Sales Plan]
                )
            )
    )
Sergii24 Frequent Visitor
Frequent Visitor

Re: Stock Cover Calculation Measure

Dear @darylmc, @v-chuncz-msft and else!

I've tried to replicate your solution and was wondering if it can be adopted to a calculated mesure with additional parameter - product location. This parameter creates an issues that depending on the filter I might have one, multiple or none (meaning all selected) locations selected.

The suggested solution creates a calculated column and doesn't work on aggregation level, because the Stock Cover calculation is not a sum or avergage but should be performed any time the context is changed.

In the example below the calculated column gives a wrong result on aggregated level, because summarize the selection instead of recalculating it again: - 10.83 days, while it should be 4.42 days: open inventory < forecast (prod of period N is considered as a part of open stock of period N+1, so doesn't have impact for Stock Coverage of week N).

11.png

 

I would be very grateful for your help!

Thank you!