MAT calculation wrong

[Silvie]

Hi, 

I posted this on Saturday as a reply on an existing topic, but didn't get any answer yet. That's why I'm creating a new message. 

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 someone help me out please? What am I doing wrong?

Kind regards,

Silvie

[lbendlin]

SAMEPERIODLASTYEAR has a different purpose.

if you want to suppress incomplete windows, use

[Silvie]

I tried to visualize the results in a line chart or table, but I'm not getting any results. 

When I try to select the organization (Org) or organization type (Org Type) it also doesn't work. I should be able to select between GROUP, VERTICAL or OU as org type. 

I've put my pbix file on my Onedrive: A016 Ecoscore - A017 Nutriscore.pbix

Maybe someone can find out what I'm doing wrong? Or should I start all over? 

[lbendlin · Accepted solution]

Your data model looks reasonable. Personally I would merge the Periode Type data into the Calendar table (like you did in the Periode Dimensies OLD.

You must use &&  , not  ||  

You must use the date from the calendar table, not from the fact table

% Nutriscore MAT = 
var d = max('Calendar'[Date])
var a = filter(ALLSELECTED(Nutriscore),Nutriscore[Date]<=d && Nutriscore[Date]>EDATE(d,-12))
return if (countrows(a)=12,divide(sumx(a,Nutriscore[OOP Nutriscore A-B]),sumx(a,Nutriscore[OOP Nutriscore]),0))

[Silvie]

Hi Ibendlin, 

The formula seems to work, I'm not getting any errors, but when I want to create a table or line chart, it is blank. 

This is how my data model looks like: 

There is a relationship between the 'Ecoscore' table and 'Periode Dimensies' table through the field 'Period' and a relationship between the 'Ecoscore' table and 'Organisatie Dimensies' table through the (Org)Key field. 

The 'Ecoscore' table is linked to the 'Calendar' table through 'Date'.

I tried removing the relationships with the Nutriscore tables and the one with the Period field but it doesn't seem to work. 

Do you have any suggestions what I can do to make this work?

[Silvie]

I also tried the formula below to avoid the "&&" but this also doesn't work. 

[Silvie]

Hi Ibendlin,

Many thx for looking into my issue. The result looks very good!

The only issue I'm encountering is that PBI doens't find my Date column after writing "&&" in the the var a line. I can only choose between my created measures. 

Is there another way to solve this? 

[Silvie]

My bad, I forgot a bracket in the formula.