Month Forward Coverage

[AA622]

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?

[Anonymous]

Hi @AA622,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[andhiii079845]

-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().