Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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] ) ) )
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.
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] ) ) )
@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 = 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 |
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] ) ) )
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
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!
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
Thank you @v-chuncz-msft
This solves the problem I mentioned in my post.
I should say, you are genius.
Regards
Mano
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.
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...
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |