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]
                )
            )
    )
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
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]
                )
            )
    )
manoranjan's avatar
manoranjan
Frequent Visitor
7 years ago

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