cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

Hi friends

 

i have a table like this

 

Name         Date                         Compare time            Check
John01-07-20 5:0001-07-20 18:00 
John01-07-20 10:00  
John01-07-20 19:00 John
John02-07-20 3:00 John
John02-07-20 9:0002-07-20 22:00 
John02-07-20 11:00  
Ron03-07-20 14:0004-07-20 3:00 
Ron03-07-20 17:00  
Ron03-07-20 20:00  
Ron03-07-20 21:00  
Ron04-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

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-08-03 152750.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

8 REPLIES 8
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-08-03 152750.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Anonymous
Not applicable

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

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
Super User
Super User

@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
Not applicable

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
Not applicable

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

ryan_mayu
Super User
Super User

@Anonymous 

try to fill down for comparedate column

1.png

then create a column

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

2.PNG





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

Proud to be a Super User!




Anonymous
Not applicable

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

because compare time are calulated column

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors