Why do Var and Measure give different results?

[Anonymous]

I want to calculate the conditional max date in a table and reuse that date for differnt tasks i.e calculate week offsets.  

CurrWeekOffset is mostly blank if I use the Measure to calculate the column.

// Measure 
Max_Ist_Date = 
  CALCULATE (
    MAX (Table[Date] ),
    FILTER( Table, Table[Source.Table] = "Something" )
   )

// Calculated Column in Table2

Max_Ist_Date = 
  CALCULATE (
    MAX (Table[Date] ),
    FILTER( Table, Table[Source.Table] = "Something" )
   )
CurrWeekOffset =   
                DATEDIFF ([Max_Ist_Date],[Date],WEEK)

However, it works if I use the same code as a VAR.

VAR Max_Ist_Date = 
  CALCULATE (
    MAX (Table[Date] ),
    FILTER( Table, Table[Source.Table] = "Something" )
   )
RETURN     
     DATEDIFF (Max_Ist_Date,[Date],WEEK)

What is going on here? 
 

[Anonymous]

Thank you for the references. I'll give MAXX a try. 

[AilleryO · Accepted solution]

Hi,

In fact quite a lot of things happening around 😉

When you use a variable in Power BI the calculation is made before the rest of the calculation and the value is stored. So what you are facing is question of evaluation context and when is calculated what.

The content (rows and columns) of the table in which you're displaying your measure has an impact on the calculation.

More information on using variable in this article or  from SQLBI.

You should try as well to work with MAXX when using measure and when iteration is needed, otherwise Power BI might consider an agregated value.

It has no impact when you are creating a calculated column because calculated columns are using the row context of the table they have been created in. So a calculated column always iterates whereas a measure can iterate, if you ask for it (for instance using  ...X functions like SUMX or MAXX or MINX...).

Hope it gives you some tracks to understand more what going on...