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

Wcys02 

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

  • Wcys02's avatar
    Wcys02
    Frequent Visitor
    2 years ago

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

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

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

        Thanks,