topic Re: Identify Leave Rows with Matching Dates and One Entry Cancelled (0 Hours) in DAX Commands and Tips

Identify Leave Rows with Matching Dates and One Entry Cancelled (0 Hours)

Sunila — Thu, 08 May 2025 15:43:44 GMT

I have a following leave dataset where employees submit leave requests in hours. Sometimes a leave request (e.g., for 4 hours) is later cancelled. In the data, this cancelled entry appears as a separate row with 0 hours and a status of "Completed", while the original request has between 1 to 8 hours.

I would like to create a DAX calculated column that identifies this kind of pair:

Two rows with the same LeaveDate (and ideally the same EmployeeID),
One of them has Hrs = 0 and Status = "Completed",
The other has Hrs between 1 and 8.

The goal is to flag the 0-hour "Completed" row as a cancellation of a previous leave request.

Any guidance or sample DAX would be greatly appreciated!

Re: Identify Leave Rows with Matching Dates and One Entry Cancelled (0 Hours)

saritasw — Thu, 08 May 2025 17:35:40 GMT

Hi Sunila ,

To identify cancellations in your leave data, you can create a calculated column that checks for two key conditions:

The current row shows 0 hours and has a "Completed" status (indicating a cancellation).

There is another row for the same employee and same leave date with a leave request between 1 to 8 hours (indicating the original request).

If both conditions are met, the 0-hour row is flagged as a cancellation of the original leave.
This approach will help you clearly distinguish between genuine leave entries and those that represent canceled requests.

***********************************************************************************************************************

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you,
Sarita

Re: Identify Leave Rows with Matching Dates and One Entry Cancelled (0 Hours)

maruthisp — Fri, 09 May 2025 00:49:03 GMT

Hi Sunila,

It would be great if you can provide some sample data. Thanks in advance.

/* ===================================================================== */

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated — thanks!

Best Regards,

Maruthi

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/

X - Maruthi Siva Prasad - (@MaruthiSP) / X
/* ===================================================================== */

Re: Identify Leave Rows with Matching Dates and One Entry Cancelled (0 Hours)

techies — Fri, 09 May 2025 07:15:30 GMT

Hi Sunila please try this calculated column

IsCancellation =

VAR CurrentEmployee = Sheet5[EmployeeID]

VAR CurrentDate = Sheet5[LeaveDate]

VAR CurrentHrs = Sheet5[Hrs]

VAR CurrentStatus = sheet5[Status]

RETURN

IF (

CurrentHrs = 0 && CurrentStatus = "Completed" &&

CALCULATE (

COUNTROWS (sheet5 ),

FILTER (

sheet5,

sheet5[EmployeeID] = CurrentEmployee &&

sheet5[LeaveDate] = CurrentDate &&

sheet5[Hrs] >= 1 && Sheet5[Hrs] <= 8

)

) > 0,

"Yes",

"No"

)