Forum Discussion

Wcys02's avatar
Wcys02
Frequent Visitor
2 years ago

Stock Cover Months

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




Need Help

12 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Wcys02
      Frequent Visitor
      2 years ago

      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's avatar
        Fowmy
        Icon for Super User rankSuper User
        2 years ago

        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

         

         






  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Wcys02
      Frequent Visitor
      2 years ago

      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.