Forum Discussion

AA622's avatar
AA622
Helper II
3 years ago

Month Forward Coverage

I need help creating a dynamic forward coverage measure. We need to see how many months we have covered for an item. The issue with the current measure below is that its fixed for 2 months/8 weeks. Our system uses 8 weeks as a standard but I want them to have some flexibility if they want to see the MFC for 4 weeks, 6 weeks 12 weeks etc. We have a date filter that uses a relative date so users can adjust it by 'last X weeks'
 
We have a calendar table that has 2 columns, month index(MIndex) and week index (WIndex), that generates a number on the calendar table to allow us to build measures that are relative to whole weeks/months. See measure below
 
MFC = VAR FILT =
    FILTER (
        ALL ( 'Calendar'[WIndex]),
        'Calendar'[WIndex] >= [CurrentWIndex] - 9
            && 'Calendar'[WIndex] <= [CurrentWIndex] - 1
    )
RETURN
    DIVIDE (
        [Physical_Inventory] ,
        DIVIDE ( CALCULATE ( [Usage], FILT ), 2 )
    )
 
CurrentWIndex = CALCULATE(MIN('Calendar'[WIndex]), FILTER(ALL('Calendar'), 'Calendar'[Date] = TODAY()))
 
I think I just need to adjust the FILT variable but am having issues quanitfying the # of months./weeks so its dynamic. Any ideas?
Need Help
Tips and Tricks

2 Replies

  • andhiii079845's avatar
    andhiii079845
    Solution Sage
    3 years ago

    -9 and -1 come from the 8 weeks right? And this has to be dynamic if you choose 4 weeks or 6 weeks, right?

    Make a table with the different values: 8,6,4,2 weeks, use it as a slicer and grap the value with SELECTVALUES().