Forum Discussion
Calculate MAT with Date filter
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))
)
It is working,
i can't find prev quater funtion like "SAMEPERIODLASTYEAR"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.
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
