Forum Discussion

Sunila's avatar
Sunila
Helper I
1 year ago
Solved

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

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 separ...
  • techies's avatar
    techies
    1 year ago

    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"
    )
techies's avatar
techies
Super User
1 year ago

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"
)