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

 

From what you have stated check if the column containing the date value is of type Date . Ensure this and everything shoiuld work fine.

 

Cheers

 

CheenuSing

Nothing's avatar
Nothing
Frequent Visitor
9 years ago

Anonymous

 

I've tried Date/Time and Date as the data type in the query editor. Still getting the error when using lastdate in the measure

 

I'm going to try recreating the project, see if that helps any

 

-Edit-

OK tried a fresh project with the same data, still the same issue. Works with another data column

 

I seem to remember reading theres a limitation in powerbi for the dates, some of the dates in this column are 12/12/9999. Would that have anything to do with it? I can't seem to find the article that mentioned it just now so not sure how to work around it

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Nothing

     

    Can you share some data.

     

    CheenuSing

     

  • jmalone's avatar
    jmalone
    Resolver III
    9 years ago

    I would check two things:

     

    1. Check the Data Type in the report view, not Query Editor. In the 'Modeling' tab you will see "Data Type." Make sure the column in question shows "Date" or "Date/Time"

     

    2. You can test the LASTDATE() function with a calculated table. This might help you determine if you 12/12/9999 is an issue. Just create a calculated table (aka "New Table") with the function:

    LastDate test = LASTDATE('table'[date field])

    The result should be a table with one row and one column showing the last date (probably 12/12/9999). If this does not work, you have an issue with the column not being recognized as a Date data type, or maybe the 9999 year is causing the issue.

    • Nothing's avatar
      Nothing
      Frequent Visitor
      9 years ago

      I replaced all the dates later than 01/01/9999 with 01/01/2050 and that seems to have done the trick, had another look for the date limitation article but couldn't find it

       

      The visualisations are displaying now but measure is displaying all the dates in my matrix as the latest date in the column rather than per aggregated result

      • 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