Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Figured out the problem, it seems the measure should be MAX(table[date]) rather than MAX(table[date].[Date])
What a hassle!
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
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
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.
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
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
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
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
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
Hi @Nothing,
Can you provide a sample file to test?
Regards,
Xiaoxin Sheng
Figured out the problem, it seems the measure should be MAX(table[date]) rather than MAX(table[date].[Date])
What a hassle!
I also used:
MaxDate = CALCULATE(MAX(Table1[date]), ALL(Table1))
Hi @Nothing
Can you share some data.
CheenuSing
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |