Forum Discussion

Nothing's avatar
Nothing
Frequent Visitor
9 years ago
Solved

Date aggregation lastdate vs max

Having an issue here with displaying the max date for each row in my matrix   When adding the date column as a value, it only gives me to aggregate it to count or count(distinct) When trying to cr...
Need Help
  • Nothing's avatar
    Nothing
    9 years ago

    Anonymous

     

    Figured out the problem, it seems the measure should be MAX(table[date]) rather than MAX(table[date].[Date])

     

    What a hassle!

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Nothing,

 

As CheenuSing said, if you share some detailed information about your issue, it will be help for troubleshooting.(e.g. sample data, screenshots, detailed info about columns or measures)

 

Regards,

Xiaoxin Sheng

Nothing's avatar
Nothing
Frequent Visitor
9 years ago

Anonymous

 

Hi Xiaoxing,

 

What I'm trying to recreate is the "Maximum" option thats available when displaying numbers as a value in a matrix but with the latest date.

 

I've created a measure with both LASTDATE and MAX functions, both return the last date of the whole column (31/12/2050) rather than the contextual value that the numeric aggregation gives as you can see below, the dates should be different

 

Thanks,

Nicola

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Nothing,

     

    According to your screenshots, it seems like you use the calculate column. Max function and LAST DATE function has different result in measure and calculate column, you can take a look at below link to know more about them.

     

    Reference link:

    Calculated Columns and Measures in DAX

     

    In addition, you can use var function to get the current value.

     

    Formula:

     

    MaxDate=
    var currdate= Max([Date])
    return

    currdate

     

    LastDate=
    var currdate= LastDate([Date])
    return

    currdate

     

     

     

    Regards,

    Xiaoxin Sheng

    • Nothing's avatar
      Nothing
      Frequent Visitor
      9 years ago

      Anonymous

       

      Its a measure I'm using rather than calculated column

       

      I'm using a matrix and trying to pivot on a group of columns with the max date as the value

       

      The formulas you suggested gives the same results as just using lastdate/max on their own unforetunately 

      MaxDate=
      var currdate= Max([Date])
      return

      currdate

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi Nothing,

         

        Can you provide a sample file to test?

         

        Regards,

        Xiaoxin Sheng