Forum Discussion
MAT calculation wrong
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:
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
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))
7 Replies
SAMEPERIODLASTYEAR has a different purpose.
if you want to suppress incomplete windows, use
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?
My bad, I forgot a bracket in the formula.
I also tried the formula below to avoid the "&&" but this also doesn't work.
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?
