Forum Discussion

DouweMeer's avatar
DouweMeer
Icon for Impactful Individual rankImpactful Individual
7 years ago
Solved

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

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. 
Show and Tell
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      DouweMeer
      Icon for Impactful Individual rankImpactful Individual
      7 years ago

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