Forum Discussion

Anil59060's avatar
Anil59060
Frequent Visitor
5 years ago

Absent / Present based on date range

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 !

1 Reply

  • stevedep's avatar
    stevedep
    Memorable Member
    5 years ago

    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.