Forum Discussion

manoranjan's avatar
manoranjan
Frequent Visitor
7 years ago
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...
Need Help
Tips and Tricks
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    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]
                )
            )
    )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.