Forum Discussion

Silvie's avatar
Silvie
Icon for Helper I rankHelper I
2 years ago
Solved

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 c...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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))
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

SAMEPERIODLASTYEAR has a different purpose.

if you want to suppress incomplete windows, use

 

 

 

Silvie's avatar
Silvie
Icon for Helper I rankHelper I
2 years ago

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?