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

Please guide me how to solve this.

 

I have weekly Data of "Planned Stock" and "Planned Sales". I should calculate what is my Stock Cover every week. 

Stock Cover is described as "How many weeks forward of Sales I can cover with the current week Stock".

 

For example

Week

Stock

Sales Plan

Stock Cover

W01

100

40

2.5

W02

120

40

3.0

W03

130

40

3.2

W04

110

40

3.0

W05

80

40

 2.8

W0610050 3.0

 

First week I have 100 Units in Stock, with which I can cover the sales for next 2.5 weeks.

I want to write a Measure to calculate this on the fly, as there are other dimensions in the Data like Product Type, Category, Price Range etc. which I ignored in this example data for the sake of simplicity.

 

Thanks in advance.