Stock Cover Months

[Wcys02]

Hi,

I have the following measure to calculate the stock months cover by each model and branch:

Stock months cover =

VAR s = SUM(Stock[Stock])

VAR w = 1

VAR t =

    FILTER ( 'Sales Plan', 'Sales Plan'[INDEX MONTH] > w )

VAR t2 =

    ADDCOLUMNS (

        t,

        "total", SUMX (

            FILTER ( t, 'Sales Plan'[INDEX MONTH] <= EARLIER ( 'Sales Plan'[INDEX MONTH] ) ),

            'Sales Plan'[Sales]

        )

    )

RETURN

    IF (

        COUNTROWS ( FILTER ( t2, [total] >= s ) )

            > 0,

        COUNTROWS ( FILTER ( t2, [total] < s ) )

            + DIVIDE (

                s

                    - MAXX ( TOPN ( 1, FILTER ( t2, [total] < s ), 'Sales Plan'[INDEX MONTH], DESC ), [total] ),

                MAXX (

                    TOPN ( 1, FILTER ( t2, [total] >= s ), 'Sales Plan'[INDEX MONTH], ASC ),

                    'Sales Plan'[Sales]

                )

            )

    )

However it does not seem to calculate the stock months cover correctly:

Example, Branch 1, Model B should result in a stock months cover of 7 instead of 1. Also, the subtotal and total are also not calculating correctly. Any guidance on this will be much appreciated.

PBIX file: https://we.tl/t-PoVXFAMd0L

[Ashish_Mathur]

Hi,

You mention that Brnach 1 Model B is a problem - you expect the answer of that to be 7.  I see some other lapses there as well.  For e.g. branch 2 Model E.  Shouldn't the answer of that be 7 (in the 7th month, the opening inventory would be consumed).  It would be ideal if you could show the exact expected result of each Branch and model combination.

Lastly, could you ensure that in each table you have a Date column.  If that is not possible, please have a month and year column in each table (from where we can create a Date column).

[Wcys02]

Hi, yes you are right, there are a few lapses, and generally the DAX code that I've written does not give me the outcome I want. I've created an excel with the same format and expected resutls by each branch and model at the different levels (https://we.tl/t-QYLx2WyuNU). As for the date column, only the sales plan has a date/timeline, in which I plan to use month number to reference the months within a year for ease of calculating the month cover.

[Ashish_Mathur]

Share both raw datasets with a Date column in both tables.

[Fowmy]

@Wcys02 

Could you explain how the Stock Cover should be calculated with logic and some examples? If you could show it Excel, it will be helpful.

[Wcys02]

Hi, 

The stock months cover is calculated as follows:

Opening stock: 3
Sales plan: Jan = 1unit, feb=1 unit, march= 2 unit,

Stock cover = 2.5 months (2months, of 2 units, and 1/2 for the 3rd month)

Opening stock: 4
Sales plan: Jan = 1unit, feb=1 unit, march= 5 unit,

Stock cover = 2.4 months (2months, of 2 units, and 2/5 for the 3rd month)

Please see excel with the examples, unfortunately I don't know how to write formula to achieve this in excel either: https://we.tl/t-QYLx2WyuNU

hope this clarifies,

thanks

[Fowmy]

@Wcys02 

Please find below the Stock Cover Measure. I have also attached the file.

Stock Coverage =
VAR __Opening = [Opening Stock]
VAR __T =
    ADDCOLUMNS (
        VALUES ( 'Sales Plan'[INDEX MONTH] ),
        "Usage", [Stock Usage],
        "Balance",
            __Opening
                - CALCULATE (
                    [Stock Usage],
                    WINDOW ( 0, ABS, 0, ALLSELECTED ( 'Sales Plan'[INDEX MONTH] ) )
                )
    )
VAR __T2 =
    FILTER ( __T, [Balance] <= 0 )
VAR __Bal =
    MINX ( __T2, [Balance] )
VAR __Month =
    MINX ( __T2, [INDEX MONTH] )
VAR __P1 =
    SWITCH (
        TRUE (),
        ISEMPTY ( __T2 ), 12,
        __Bal = 0, __Month,
        __Month - 1
            + MINX (
                FILTER ( __T, 'Sales Plan'[INDEX MONTH] = __Month ),
                DIVIDE ( [Usage] + [Balance], [Usage] )
            )
    )
RETURN
    __P1

[Wcys02]

Many thanks for this! its spot on across model and branch (with right aggregation). How would I add get the stock coverage code to start calculating from a certain index month instead of the first month based on this code?

My plan is to update the report based on the actual month by creating the following measure to work out actual month number:
Actual month index number = IF((YEAR(TODAY())=2023),MONTH(TODAY()),MONTH(TODAY())+12)

Whereby Jan-Dec '23 = month 1 to 12
Jan-Dec '24 = month 13 to 24

I've tried then to incorporate this into the code but does not seem to be able to filter from month index 2 onwards, example by changing this part to:

VAR __T =
ADDCOLUMNS (
CALCULATETABLE(
VALUES('Targets (2)'[INDEX MONTH]),
'Targets (2)'[INDEX MONTH] >= 2

Thanks again,

[Fowmy]

@Wcys02 

Sorry, your requirement isn't clear enough, please share an example in Excel.

[Wcys02]

Hi, apologies for not being clear. I basically want the reference index month to be dynamic based on the current month. For example, in December where the index/month number would be 12, it will start to calculate the stock cover using the sales plan based on index month 12 and onwards.

Excel link with example:https://we.tl/t-LJwkepZ63O

Hope this clarifies,

Many thanks

[Fowmy]

@Wcys02 

In your Excel file, the data is spread only across 12 months per branch and model, the required scenario cannot be tested without having data for 24 months, please attach a files (Excel and PBI) , with proper dates instead of month numbers.  

[Wcys02]

Link to the revised file with an additional date column in both excel and PBI:
https://we.tl/t-Wm57l4fqco

Thanks,

[Fowmy]

@Wcys02 

Let me have a look at time later and get back to you.