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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
rliu
Helper I
Helper I

create a measure of latest date in a column

I want to put a tile on my report carrying the latest date in the data used.

 

I've tried several ways:

 

1: Measure = LASTDATE(my_table[Date])

Result: no error, blank visual

 

2: Measure = LASTDATE(DISTINCT(my_table[DateTime]))

Result: MdxScript(Model) (1, 46) Calculation error in measure 'gen_edi_stats_addl'[Measure]: A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.

 

 

3: Measure = LASTDATE(DISTINCT(my_table[DateTime]))

Result: Still the same as 2.

 

Any idea? Thanks!

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Ya, that first one looks correct to me, is it possible my_table[date] has blanks?

 

If you want to try something else... you can use MAX( ) instead of LASTDATE( ).

 

View solution in original post

v-sihou-msft
Microsoft Employee
Microsoft Employee

@rliu

 

I reproduced your issue. When you put the a datetime column in LASTDATE() function, if there are same date with different timestamp, it will throw above error.

 

Capture9.PNG

Capture88.PNG

So in your scenario, please use MAX() function for this date/time column as @Anonymous suggested.

 

Regards,

View solution in original post

7 REPLIES 7
v-sihou-msft
Microsoft Employee
Microsoft Employee

@rliu

 

I reproduced your issue. When you put the a datetime column in LASTDATE() function, if there are same date with different timestamp, it will throw above error.

 

Capture9.PNG

Capture88.PNG

So in your scenario, please use MAX() function for this date/time column as @Anonymous suggested.

 

Regards,

@v-sihou-msft it looks like the second half of your response is missing. And just when it was getting to the good part!

It's quite strange that part of my reply is missing. I have edited my reply.

Anonymous
Not applicable

Ya, that first one looks correct to me, is it possible my_table[date] has blanks?

 

If you want to try something else... you can use MAX( ) instead of LASTDATE( ).

 

vickyprudhvi
Helper IV
Helper IV

Hi,

LastDate function can be used on on Date table because Lastdate needs Distinct dates and there shouldnt be any duplicates.

Create a date table and use it.

 

Correct me if I am worng

KGrice
Memorable Member
Memorable Member

What kind of visual are you using? When I use your first formula to create a measure, then drag it onto the report view, I initially get a blank visual. However, that's because the default visual is a chart, and there's no data to plot. If I change it to a table (or card, or I think anything that's not a chart), I get the latest date in my table.

 

LastDate.PNG

 

 

Hi, what should I do when last date has bank values in other columns? the date is 2018-04-24 but last value eg. sales are in row 2018-04-23, so LASTDATE(Table[Date]) is still 2018-04-23. Please help 🙂

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.