Forum Discussion
Solved
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 D...
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] ) ) )
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]
)
)
)
There is only problem with , instead of ; 😉 thanks for help