Absent / Present based on date range

[Anil59060]

I have 10 employees in my Dept. Each one submits his leave plan begining of a year which includes Emp No, Leave Start and Leave end dates (Emp Table). I want to see on a selected date if a particular employee is available or not using calculated column as given below;

Emp 1 =
VAR _Check= SELECTEDVALUE(Emp No)
VAR _max = SELECTEDVALUE('Date'[Date])
VAR _min = SELECTEDVALUE('Date'[Date])
RETURN
    IF (_Check= "Emp 1" && _min>=SELECTEDVALUE(Emp[Leave Start]) && _max<=SELECTEDVALUE(Emp[Leave End]),"Leave","Available")

I am getting "Available" on all dates. Let me know what is wrong in above !

[stevedep]

Hi,

Below works;

__Available = 
var _selDate = SELECTEDVALUE('Date'[Date])
return
if (
COUNTROWS(FILTER('Emp Table', 'Emp Table'[Leave Start] >= _selDate && _selDate <= 'Emp Table'[Leave End] )) > 0 , "Not Available", "Available")

As seen here:

Please note the data model and the helper columns to join, value is 1 in both. 

The file. 

Please mark as solution if so. Thumps up for the effort is appreciated.

Kind regards, 

Steve.