Total 12 MAT Last 2 Year from ( (May 2018-Jun 2017)

[Chanleakna123]

Hi All , 

I wanna create formula :

*** 12 MAT ideally from (May 2018-Jun2017). I wanna compare the trend between 12MAT year to Year. 

Right now i have acheived 2 Formulas below , And Would like to create another Formula of 12 MAT from (May 2018-Jun2017). 

**12 MAT. ( May2020-Jun2019 ) = IF(ISBLANK('UC & NSR Type'[$NSR Or UC]),BLANK(),CALCULATE([$NSR Or UC],DATESINPERIOD('445 Calendar'[Date],ENDOFMONTH('445 Calendar'[Date]),-12,MONTH)))

**12MAT L1Y ( May 2019-Jun 2018) = CALCULATE('UC & NSR Type'[$NSR Or UC],DATESINPERIOD('445 Calendar'[Date],ENDOFMONTH(DATEADD('445 Calendar'[Date],-12,MONTH)),-12,Month))

** 12MAT L2Y (May 2018-Jun2017) = ?????

[MFelix]

Hi @Chanleakna123 ,

Instead of making it 12 months on each of the measure why don't you make it a year calculation:

12MAT L1Y ( May 2019-Jun 2018) =
CALCULATE (
    'UC & NSR Type'[$NSR Or UC],
    FILTER (
        ALL ( '445 Calendar'[Date] ),
        '445 Calendar'[Date]
            <= ENDOFMONTH ( DATEADD ( '445 Calendar'[Date], -1, YEAR ) )
            && '445 Calendar'[Date]
                >= ENDOFMONTH ( DATEADD ( '445 Calendar'[Date], -2, YEAR ) )
    )
)


12MAT L2Y ( May 2018-Jun 2017) =
CALCULATE (
    'UC & NSR Type'[$NSR Or UC],
    FILTER (
        ALL ( '445 Calendar'[Date] ),
        '445 Calendar'[Date]
            <= ENDOFMONTH ( DATEADD ( '445 Calendar'[Date], -2, YEAR ) )
            && '445 Calendar'[Date]
                >= ENDOFMONTH ( DATEADD ( '445 Calendar'[Date], -3, YEAR ) )
    )
)

Be aware that his may need some adjustments, especcially on the start date that is set to end of month (and probably you need it to start of month.

[Chanleakna123]

@MFelix  , hi , i have used your calculation , it doesn't work from my side , when i put next to 2 other calculation i haved shared. 

Now I have year Filter 2020 , When i put yours formula in , it shown blank. 

[MFelix]

@Chanleakna123 

Can you share a sample file?