Stock Cover Calculation Measure

Thank you v-chuncz-msft :smileyvery-happy:

This solves the problem I mentioned in my post. 

I should say, you are genius.

Regards

Mano

v-chuncz-msft

I tried implementing this in my production Application. I was expecting this Stock Cover calculation to happen on-the-fly based on my other selections like Product Category, Store Type, Territory etc.

But when we add a column it calculates the Stock Cover as a static column to the Table. This is a problem in this case, as the Sum(Stock) and Sum(Sales) varies depending on the filters made in the Slicers.

Any workaround you can suggest? Please help.

Regards

Mano

---

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

Thanks for reply venug20

But what I want to achieve is different.

For example, my current week (week 1) Stock is 100 units.

Sales plan

W2 = 40,

W3=50,

W4=50

With the current stock of 100 units, I can sell upto 2.1 weeks forward.

Your solution takes care of only the Current Weeks sales. So this will not work in my scenario.