Forum Discussion

Silvie's avatar
Silvie
Helper 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
Super User
2 years ago

SAMEPERIODLASTYEAR has a different purpose.

if you want to suppress incomplete windows, use

 

 

 

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

    My bad, I forgot a bracket in the formula.