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 Pla...
Need Help
Fowmy's avatar
Fowmy
Super 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
    Super 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

     

     






    • Wcys02's avatar
      Wcys02
      Frequent Visitor
      2 years ago

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

        Wcys02 

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