cancel
Showing results for
Did you mean:
Highlighted
manoranjan 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

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

## Re: Stock Cover Calculation Measure

You may check the following DAX.

```Column =
VAR s = Table1[Stock]
VAR w = Table1[Week]
VAR t =
FILTER ( Table1, Table1[Week] > w )
VAR t2 =
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

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

venug20 Member

## Re: Stock Cover Calculation Measure

@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 Frequent Visitor

## Re: Stock Cover Calculation Measure

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

## Re: Stock Cover Calculation Measure

You may check the following DAX.

```Column =
VAR s = Table1[Stock]
VAR w = Table1[Week]
VAR t =
FILTER ( Table1, Table1[Week] > w )
VAR t2 =
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

## Re: Stock Cover Calculation Measure

Thank you @v-chuncz-msft This solves the problem I mentioned in my post.

I should say, you are genius.

Regards

Mano

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

Regards

Mano

darylmc Frequent Visitor

## Re: Stock Cover Calculation Measure

@v-chuncz-msft wrote:

You may check the following DAX.

```Column =
VAR s = Table1[Stock]
VAR w = Table1[Week]
VAR t =
FILTER ( Table1, Table1[Week] > w )
VAR t2 =
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 Frequent Visitor

## Re: Stock Cover Calculation Measure

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

## 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). I would be very grateful for your help!

Thank you!