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
v-chuncz-msft wrote:

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]
                )
            )
    )

 

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

StockSales PlanStock Cover
AW0110040 
AW0212040 
AW0313040 
AW0411040 
AW058040 
AW0610050 
AW0712050 
BW0115080 
BW0218080 
BW0319580 
BW0416580 
BW0512080 
BW06150100 
BW07180100 
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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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