Forum Discussion
Anonymous
7 years agoNot applicable
Stock Cover Calculation
Someone previously provided the following DAX solution to calculate Stock Days cover:
Can someone please explain to me how to get this to work?
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]
)
)
)
4 Replies
- v-lili6-msftCommunity Support
hi, Anonymous
Could you share some simple sample data for it?
Just from the formula, it's difficult to explain it.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Best Regards,
Lin- AnonymousNot applicable
Demand Supply Closing MATNR Year_WeekNo Remain-WK13 Remain-WK14 Remain-WK15 Days_Cover 0 5993 5993 ABC 2019-13 5993 37 703 5811 11101 ABC 2019-14 5290 11101 60 608 0 10493 ABC 2019-15 4682 10493 10493 69 1420 0 9073 ABC 2019-16 3262 9073 9073 810 0 8263 ABC 2019-17 2452 8263 8263 2170 23040 29133 ABC 2019-18 282 6093 6093 1257 0 27876 ABC 2019-19 -975 4836 4836 1240 0 26636 ABC 2019-20 3596 3596 1720 0 24916 ABC 2019-21 1876 1876 1120 0 23796 ABC 2019-22 756 756 1440 0 22356 ABC 2019-23 -684 -684 417 0 21939 ABC 2019-24 226 0 21713 ABC 2019-25 410 0 21303 ABC 2019-26 1052 0 20251 ABC 2019-27 Formula for Days Cover
=COUNT(F3:F7)*7+ROUND(F7/A8*7,0)
- v-lili6-msftCommunity Support
hi, Anonymous
If this formula is work on the data above, I think there is no connection between the data and the formula.
Please check it if you uploaded the wrong data.
Best Regards,
Lin