Solved: Stock Cover Calculation Measure - Page 2

manoranjan · ‎10-05-2018

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 Data is like this

Week	Stock	Sales Plan
W01	100	40
W02	120	40
W03	130	40
W04	110	40
W05	80	40
W06	100	50
W07	120	50

In the above example, Week 1 Stock = 100 units and with that I can cover my sales for next 2.5 weeks. ( In other words I can sell w2, w3, and 0.5 of w4).

I want to build a measure that can show below table

Week	Stock	Sales Plan	Stock Cover
W01	100	40	2.5
W02	120	40	3.0
W03	130	40	3.2
W04	110	40	2.6

Please help, how to calculate the Stock Cover Measure.

Thanks in advance.

v-chuncz-msft · ‎10-10-2018

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Stock Cover Calculation Measure

Helpful resources

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly