Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nothing
Frequent Visitor

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

 

1 ACCEPTED SOLUTION

@v-shex-msft

 

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

 

What a hassle!

View solution in original post

12 REPLIES 12
CheenuSing
Community Champion
Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

 

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

aggregatetypes.jpg

 

agedates.jpg

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

 

Capture.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

 

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.