Calculate MAT with Date filter

[multt0]

I made MAT measure like below

@mat = CALCULATE(SUM('Fct_Sales_IMS'[Values_LC]),

               DATESINPERIOD( 'CALENDAR'[Date], MAX('CALENDAR'[Date]), -12, MONTH))

1) I want to another measure for last year like below

I tried like below,,

VAR year_ = SELECTEDVALUE('CALENDAR'[Year])

VAR quarter_ = SELECTEDVALUE('CALENDAR'[Quarter])

RETURN
MAT_y-1 = 

CALCULATE(

CALCULATE(SUM('Fct_Sales_IMS'[Values_LC]),

        DATESINPERIOD( 'CALENDAR'[Date], MAX('CALENDAR'[Date]), -12, MONTH)),

        FILTER('CALENDAR', YEAR([Date]) = year_  -1))

Thanks

[FreemanZ · Accepted solution]

Hi   @multt0 

try to create the measure with this (the VAR part seems unnecessary):

MAT_y-1 = 
CALCULATE(
    SUM('Fct_Sales_IMS'[Values_LC]),
    SAMEPERIODLASTYEAR(DATESINPERIOD('CALENDAR'[Date], MAX('CALENDAR'[Date]), -12, MONTH))
)

[Silvie]

Hi, 

I'm new at calculating rather complex formulas. I need to calculate the MAT based on a measure. 

Table with data see below including what we expect the MAT result should look like. 

Date	OOP - AB	OOP - with Ecoscore	Expected MAT result
01/02/2022	62006571,96	153290854,87
01/03/2022	75885416,73	203187723,93
01/04/2022	81008926,08	246855401,59
01/05/2022	80554289,31	242783685,49
01/06/2022	80889841,36	244188668,79
01/07/2022	83651207,72	246026973,7
01/08/2022	90373368,73	258385872,05
01/09/2022	88898086,38	252980248,4
01/10/2022	100886308,19	355064447,87
01/11/2022	114580142,92	368026113,14
01/12/2022	134022067,66	446482406
01/01/2023	120197150,76	348696194,37	33,06% = sum of ' OOP - AB' from 02/2022 till 01/2023  divided by the sum of 'OOP - with Ecoscore' from 02/2022 till 01/2023
01/02/2023	116118961,65	335621916,26	32.89% =  sum of ' OOP - AB' from 03/2022 till 02/ 2023 divided by the sum of 'OOP - with Ecoscore' from 03/2022 till 02/ 2023
01/03/2023	138040772,3	402175919,59	32.80%
01/04/2023	138203247,47	386740063,91	33.09%
01/05/2023	121148045,18	302451488,75	33.62%
01/06/2023	146359040,74	380171755,01	34,11%
01/07/2023	136880971,51	356216795,69	34.48%
01/08/2023	140059604,92	358827668,56	34,83%
01/09/2023	141468248,64	364874261,48	35,14%
01/10/2023	142689405,06	366071051,77	36.00%
01/11/2023	138292652,57	358040963,32	36.62%
01/12/2023	159139285,21	440116959,08	37.24%

I used the same logic as mentioned in this solution, using 'SAMEPERIODLASTYEAR' but I'm getting other results. 

This is the formula I used to calculate the MAT: 

% Ecoscore MAT Y-1 = calculate(

Ecoscore[% Ecoscore met A+B],

SAMEPERIODLASTYEAR(DATESINPERIOD(Ecoscore[Date],max(Ecoscore[Date]),-12,month))

)

The formula for the % Ecoscore met A+B measure looks like this (and is correct) 

=Calculate(

Sum(Ecoscore[OOP]),

Filter('Ecoscore','Ecoscore'[Ecoscore] In {"A","B"}))/

Calculate(Sum('Ecoscore'[OOP]),Filter('Ecoscore','Ecoscore'[Ecoscore] In {"A","B","C","D","E"})

)

Could you help me out please? What am I doing wrong?

Kind regards,

Silvie

[multt0]

It is working,
i can't find prev quater funtion like "SAMEPERIODLASTYEAR"

[FreemanZ]

there is no such function😂, try to use DATEADD. It is more flexible. Most Time Intelligence functions return a list of dates, so be brave to nest them.