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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.