Compare time between column but use its lastest value with condition by DAX

[Anonymous]

Hi friends

i have a table like this

Name	Date	Compare time	Check
John	01-07-20 5:00	01-07-20 18:00
John	01-07-20 10:00
John	01-07-20 19:00		John
John	02-07-20 3:00		John
John	02-07-20 9:00	02-07-20 22:00
John	02-07-20 11:00
Ron	03-07-20 14:00	04-07-20 3:00
Ron	03-07-20 17:00
Ron	03-07-20 20:00
Ron	03-07-20 21:00
Ron	04-07-20 5:00		Ron

how i create the check column that will show name in its in case of date are after latest compare time indivisually

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

First go to query editor>add column>index column;

Then create a measure as below:

Measure = 
var _maxcomparetime=CALCULATE(MAX('Table'[Compare time]),FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])&&'Table'[Index]<=MAX('Table'[Index])))
Return
IF(MAX('Table'[Date])>_maxcomparetime,MAX('Table'[Name]),BLANK())

Finally you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft thanks you so much and nice description, i will try your advices on my BI asap.

[v-kelly-msft]

Hi  @Anonymous ,

OK,if it helps solve the issue,could you pls mark the reply as answered to close it?😊

Much appreciated.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , Try a new column like

new colum =
var _date = maxx(filter(Table,Table,[Name] = earlier([Name]) && [date].date =earlier([date]).date),[Compare time])
return
if([Date]>_date, [Name], blank())

If earlier([date]).date do not work then have date column first

new date = [date].date

new colum =
var _date = maxx(filter(Table,Table,[Name] = earlier([Name]) && [new date] =earlier([new date])),[Compare time])
return
if([Date]>_date, [Name], blank())

[Anonymous]

thank for your solution but i have a little condition that if rows are shuffle and didnt sorted by date, what should i do? @amitchandak 

[Anonymous]

@amitchandak in case of first row of compare time is blank, how you do adjusted DAX?

[ryan_mayu]

@Anonymous 

try to fill down for comparedate column

then create a column

Column = if(Sheet12[Compare time]<='Sheet12'[Date ],Sheet12[Name],blank())

[Anonymous]

thank you @ryan_mayu for your answer but is it possible to use only DAX?

because compare time are calulated column