cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 1
Highlighted
Solution Sage
Solution Sage

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:

empavail.jpgmodel.jpg

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. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors