create a measure of latest date in a column

[rliu]

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!

[Anonymous · Accepted solution]

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( ).

[v-sihou-msft · Accepted solution]

@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.

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

Regards,

[KGrice]

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

[v-sihou-msft]

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

[Anonymous · Accepted solution]

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]

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]

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.

[awitkowska]

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 🙂