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