Rank based on 2 columns - "Date" and "Date and Time"

[Honne2021]

The process I need is rank based on Effective Date based on ID and Process Completed Date and Time: find the employee's ID number, if the employee's effective dates are the same, rank the date based on the Process Completed Date. I would need a calculated column to get the rank...also...should I separate the time in another column?

Thank you very much! 🙂 

ID Number	Effective Date	Process Completed Date and Time	Team
Emp 100	Jan 1 2022	Jan 31 2022   9:00	Team Red	Team Red is the result I should get because even if both effective dates are the same for the employee, I need to follow the most recent Process Completed Date to break the tie
Emp 100	Jan 1 2022	Jan 2 2022   20:51	Team Blue
Emp 200	Jan 1 2022	Dec 20 2022 8:00	Team Red
Emp 200	Jan 02 2022	Dec 20 2022 8:00	Team Blue	Team Blue is the result I need to get because it is the most recent data based on Effective Date.

[mangaus1111]

Hi @Honne2021 ,

see my pbi file

https://1drv.ms/u/s!Aj45jbu0mDVJizrPazbtUOF1DO2D?e=8Nx7oj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Honne2021]

Hi, thank you for the file. However, there are still ties per ID Number :S 

[Honne2021]

Thank you, this worked!

[mangaus1111 · Accepted solution]

Hi @Honne2021 ,

see my new pbi file. I hope now it works

https://1drv.ms/u/s!Aj45jbu0mDVJi0EkceXt-tS8ZiZY?e=gxSNoq

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vojtechsima]

Hi, @Honne2021 ,
This should work:

Team New = 
var Employee = Teams[ID Number]
var EffectiveDate = Teams[Effective Date]
var ProcessDatetime = Teams[Process Completed Date and Time]

var Max_EffectiveDate = MAXX(FILTER(Teams, Employee = Teams[ID Number]), Teams[Effective Date])
var Max_ProcessDateTime = MAXX(FILTER(Teams, Employee = Teams[ID Number]), Teams[Process Completed Date and Time])
var CheckForSameProcessDateTime = COUNTROWS(FILTER(Teams, Teams[ID Number] = Employee && Max_ProcessDateTime = Teams[Process Completed Date and Time]))

var Check = 
SWITCH(TRUE(),
    (Teams[ID Number] = Employee && Teams[Effective Date] < Max_EffectiveDate) ||  (Teams[ID Number] = Employee  && Teams[Effective Date] = Max_EffectiveDate && Teams[Process Completed Date and Time] = Max_ProcessDateTime && CheckForSameProcessDateTime < 2), "Team Red",
    Teams[ID Number] = Employee  && Teams[Process Completed Date and Time] < Max_ProcessDateTime , "Team Blue",
     Teams[ID Number] = Employee && Teams[Effective Date] = Max_EffectiveDate, "Team Blue"
)


return Check

[Honne2021]

Hi! I'm sorry I think I didnt explain myself well. What I mean is that I would need to rank the events wherein the results would be a ranking based on Effective Date and if there are ties, it should be settled using the Process Completed Date and Time. 

Once this is done, I will just use a measure that will pick up the most recent (highest rank) per employee and use it as that to reference their Team.

ID Number	Effective Date	Process Completed Date and Time	Team		Rank
Emp 100	Jan 1 2022	Jan 31 2022   9:00	Team Red	Team Red is the result I should get because even if both effective dates are the same for the employee, I need to follow the most recent Process Completed Date to break the tie	2
Emp 100	Jan 1 2022	Jan 2 2022   20:51	Team Blue		1
Emp 200	Jan 1 2022	Dec 20 2022 8:00	Team Red		1
Emp 200	Jan 02 2022	Dec 20 2022 8:00	Team Blue	Team Blue is the result I need to get because it is the most recent data based on Effective Date.	2

Thank you for your help so far! I didnt imagine the codes to be this long. I'm sure it took a lot of effort since I did not attach a file as reference.

[vojtechsima]

Hi, @Honne2021,
what about this:

Rank New = 
var Employee = Teams[ID Number]
var EffectiveDate = Teams[Effective Date]

var IsSameEffectiveDate = COUNTROWS(FILTER(Teams, [ID Number] = Employee && Teams[Effective Date] = EffectiveDate))
var Check = 
IF(IsSameEffectiveDate >= 2, 
    RANKX(FILTER(Teams, Teams[ID Number] <= EARLIER(Teams[ID Number])), 
    Teams[Process Completed Date and Time],, ASC,Dense), RANKX(FILTER(Teams, Teams[ID Number] <= EARLIER(Teams[ID Number])), Teams[Effective Date],,ASC,Dense)
)


return Check