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]
                )
            )
    )
darylmc's avatar
darylmc
Frequent Visitor
7 years ago

Got it, in the end. :manhappy:

 

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]
                )
            )
    )
Sergii24's avatar
Sergii24
Super User
7 years ago

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!