Stock Cover Calculation Measure

[manoranjan]

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.

[Anonymous]

Can anybody help me Stock COver calcultion as  a measure which would show the Weeks Cover at aggregate level and also when the filters apply . My data has  Weeks, Total demand, total suply, closing stock, product , location.

 With Calculated column i get an error for out of memory when i try to include the location along with week and SKU.

[v-chuncz-msft · Accepted solution]

@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]
                )
            )
    )

[AnnaBancyr]

There is only problem with , instead of ; 😉 thanks for help

[darylmc]

---

@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	Stock	Sales Plan	Stock Cover
A	W01	100	40
A	W02	120	40
A	W03	130	40
A	W04	110	40
A	W05	80	40
A	W06	100	50
A	W07	120	50
B	W01	150	80
B	W02	180	80
B	W03	195	80
B	W04	165	80
B	W05	120	80
B	W06	150	100
B	W07	180	100

[darylmc]

Got it, in the end. 

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]
                )
            )
    )

[Anonymous]

Hi @darylmc 

Many thanks for the solution, but do you know if there's any reason this wouldn't work in Power Pivot? It works perfectly in Power BI, but I'm just wanting to use the same calculated column within Power Pivot for Excel and I'm not sure why it's producing the same results.

Below are the results from Power BI and then Power Pivot in Excel. Below are the differing results within Power BI and Power Pivot in Excel.

The code is the same in both:

Weeks Cover = 
VAR s = 'Stock Movements'[Forecast Stock in Hand]
VAR w = 'Stock Movements'[Date]
VAR x = 'Stock Movements'[Product Code]
VAR t =
    FILTER ( 'Stock Movements', 'Stock Movements'[Date] > w && 'Stock Movements'[Product Code] = x)
VAR t2 =
    ADDCOLUMNS (
        t,
        "total", SUMX (
            FILTER ( t, 'Stock Movements'[Date] <= EARLIER ( 'Stock Movements'[Date] )&&'Stock Movements'[Product Code]=EARLIER('Stock Movements'[Product Code])),
            'Stock Movements'[Forecast]
        )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( t2, [total] >= s ) )
            > 0,
        COUNTROWS ( FILTER ( t2, [total] < s ) )
            + DIVIDE (
                s
                    - MAXX ( TOPN ( 1, FILTER ( t2, [total] < s ), 'Stock Movements'[Date], DESC ), [total] ),
                MAXX (
                    TOPN ( 1, FILTER ( t2, [total] >= s ), 'Stock Movements'[Date], ASC ),
                    'Stock Movements'[Forecast]
                )
            )
    )

Any ideas would be greatly appreciated.

Many thanks,

Paddy

[Sergii24]

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).

I would be very grateful for your help!

Thank you!

[manoranjan]

@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

[manoranjan]

Thank you @v-chuncz-msft 

This solves the problem I mentioned in my post. 

I should say, you are genius.

Regards

Mano

[manoranjan]

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
W06	100	50	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]

@manoranjan

use below formula to achive..

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

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

[manoranjan]

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.