Forum Discussion
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 create a measure with lastdate e.g. LASTDATE(qualifications[valid_to_date]) it gives me an error "An invalid numeric representation of a date value was encountered"
When trying to create a measure with max e.g. max(qualifications[valid_to_date].[Date]) it gives me the max of the whole table rather than just the row
In the query editor the column im trying to aggregate is defined as Date/Time
Does anyone know where i'm going wrong? Any help would be appreciated
Anonymous
Figured out the problem, it seems the measure should be MAX(table[date]) rather than MAX(table[date].[Date])
What a hassle!
12 Replies
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
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
