Why doesn't it work? YTD - max (date) vs custom column max (date)

[DouweMeer]

So I'm trying to create a YTD measure and this is :

Sales this year =

calculate ( 

   sum ( 'Price_Realisation'[USD Conversion] )

   , filter (

      'Price_Realisation'

      ,'Price_Realisation'[YTD year] = year ( max ( [INVOICE_DT] ) )

      )

   )

- USD conversion is the column where all sales was changed to USD.

- YTD column is a custom column that contain an if expression to check if it fits within the ytd date range and returns the year

- Invoice_dt is the actual invoice date. 

But apparently this one doesn't work. Creating a separate column with max ([invoice_dt]) seems to be working. 

Sales past year =

calculate (

   sum ( 'Price_Realisation'[USD Conversion] )

   , filter (

      'Price_Realisation'

      , 'Price_Realisation'[YTD year] = year ( 'Price_Realisation'[Max date] )

      )

   )

Can someone explain me why this is the case? I'm figuring that it has to do with that it searches the max date within a selection that is being filtered. 

[v-frfei-msft · Accepted solution]

Hi @DouweMeer,

In your first  formula, we can only get the max year or the current row by max ( [INVOICE_DT] ). So we should update your formula as below.

Sales this year =
VAR _maxyear =
    YEAR ( MAXX ( ALL ( yourTABLE ), [INVOICE_DT] ) )
RETURN
    CALCULATE (
        SUM ( 'Price_Realisation'[USD Conversion] ),
        FILTER ( 'Price_Realisation', 'Price_Realisation'[YTD year] = _maxyear )
    )

If any other issue, kindly share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.

Regards,

Frank

[DouweMeer]

That actually looks very logical. I notice more and more that even though DAX looks a lot like Excel, it isn't Excel :). Thanks.