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,

     

    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's avatar
      Silvie
      Icon for Helper I rankHelper I
      2 years ago

      My bad, I forgot a bracket in the formula.

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

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

     

     

  • 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?

     

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

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