topic Re: Filtering Dates in DAX Commands and Tips

Filtering Dates

Barry_Sophus — Sat, 03 Feb 2024 04:39:38 GMT

For each User ID, I need to identify the rows where LogInDates occurs on the same day. After that I need to identify the closest LogOutDates that occurs on the same day or after. There can be only two dates in a pair but there can be multiple pairs for each UserId. The LogOutDate and is not dependant on the LoginDate. How do I do this in Dax?

User Id	LogInDate	LogOutDate	Expected
A	12/29/2013	1/1/2014
A	12/30/2014	1/1/2015	x
A	12/29/2014	1/1/2015
A	12/30/2014	1/1/2016	x
B	1/30/2015	2/1/2015	x
B	1/30/2015	2/1/2015	x
B	1/30/2015	4/1/2015
B	1/30/2015	3/1/2015
C	4/26/2016	5/1/2016
C	5/27/2016	6/1/2016	x
C	5/27/2016	6/1/2016	x
C	5/28/2016	6/2/2016
C	5/29/2016	6/3/2016

Re: Filtering Dates

123abc — Sat, 03 Feb 2024 05:42:45 GMT

To achieve this in DAX (Data Analysis Expressions), you can create a calculated column that flags the rows where LogInDate and LogOutDate occur on the same day and then identify the closest LogOutDate that occurs on the same day or after the LogInDate. Below is the DAX code to implement this logic:

PairFlag =
VAR CurrentUserId = 'YourTable'[User Id]
VAR CurrentLogInDate = 'YourTable'[LogInDate]
VAR SameDayLogOut =
FILTER(
'YourTable',
'YourTable'[User Id] = CurrentUserId &&
'YourTable'[LogOutDate] >= CurrentLogInDate &&
DATEDIFF('YourTable'[LogInDate], CurrentLogInDate, DAY) = 0
)
VAR ClosestLogOutDate =
MINX(
FILTER(
SameDayLogOut,
'YourTable'[LogOutDate] >= CurrentLogInDate
),
'YourTable'[LogOutDate]
)
RETURN
IF(
'YourTable'[LogOutDate] = ClosestLogOutDate,
"x",
BLANK()
)

Replace 'YourTable' with the actual name of your table in your Power BI model. This DAX code creates a calculated column called PairFlag which checks for each row if there exists a LogOutDate on the same day as the LogInDate, and then finds the closest LogOutDate on the same day or after the LogInDate. If the LogOutDate is the closest one, it flags it with "x", otherwise, it remains blank.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Filtering Dates

ThxAlot — Sat, 03 Feb 2024 07:33:07 GMT

Simple enough

Re: Filtering Dates

Barry_Sophus — Tue, 06 Feb 2024 14:59:22 GMT

My appologies. I don't think what I explained was clear. What I am wanting is For each User ID, I want to determine if the LogoutDate is on/after the current row. If it is then flag those two rows then go to the next unflagged row and perform the same analysis.