Forum Discussion
Conditional column - rows condition for Time comparision
Hi team, I am a newbie and wants to compare and match time for vehicle entry and exits to their latest ones to calculate the turn around time. Each vehicle has multiple entry and exit time stamp and...
Thanks for the reply from Ashish_Mathur and Greg_Deckler , please allow me to provide another insight:
Hi Anonymous ,Here are the steps you can follow：
1. Create calculated column.
Rank = RANKX( FILTER(ALL('Table'), 'Table'[Driver]=EARLIER('Table'[Driver])),[Time],,ASC)Mod = MOD([Rank],2)Group = SUMX( FILTER(ALL('Table'), 'Table'[Driver]=EARLIER('Table'[Driver])&&'Table'[Rank]<=EARLIER('Table'[Rank])),[Mod])Match = var _match_entrytime= MAXX( FILTER(ALL('Table'), 'Table'[Driver]=EARLIER('Table'[Driver])&&'Table'[Group]=EARLIER('Table'[Group])&&'Table'[Rank]=EARLIER('Table'[Rank])+1),[Time]) return IF( _match_entrytime>=[Time] || _match_entrytime=BLANK(),TRUE(),FALSE())2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks for the reply from Ashish_Mathur and Greg_Deckler , please allow me to provide another insight:
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated column.
Rank =
RANKX(
FILTER(ALL('Table'),
'Table'[Driver]=EARLIER('Table'[Driver])),[Time],,ASC)Mod =
MOD([Rank],2)Group =
SUMX(
FILTER(ALL('Table'),
'Table'[Driver]=EARLIER('Table'[Driver])&&'Table'[Rank]<=EARLIER('Table'[Rank])),[Mod])Match =
var _match_entrytime=
MAXX(
FILTER(ALL('Table'), 'Table'[Driver]=EARLIER('Table'[Driver])&&'Table'[Group]=EARLIER('Table'[Group])&&'Table'[Rank]=EARLIER('Table'[Rank])+1),[Time])
return
IF(
_match_entrytime>=[Time] || _match_entrytime=BLANK(),TRUE(),FALSE())2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly