Monthly Utilization with Employee Start and End Date

[ShortyAO]

I am still encountering difficulties on getting a monthly utilization to calculate including an employee Start and End Date.  I have the following DAX to calculate the available hours based on an emplyee table and calender table.  However, my visual still reflects available hours for when an employee is not hired or terminated for that period.  

Tables in Model:

Calender - have a column reflecting the hours available to work on weekdays

Start_End_Date - Employee Roster has employee #, Employee Name, Start date, end date, and Status (active/terminated)

100% Report - Timesheet of employees reporting billable and non billable hours

These are the measures I currently using:

Billable Hours = CALCULATE(sum('100%Report'[Hours]),'100%Report'[Time Category] = "Billable")+0

NEW Hours Available = CALCULATE(sum(Calender[2080 PerDay]),FILTER(Calender,Calender[Date]>=FIRSTDATE(Start_End_Dates[Adj Hire Date])),FILTER(Calender,Calender[Date]<=LASTDATE(Start_End_Dates[Termination Date])))

New Utilization % = DIVIDE([Billable Hours],[NEW Hours Available])

Here is what my visual looks like 

I would be expecting to see no available hours for Kaveh from Jan to May since he started in June.  This becomes an issue when I then look at the utilization % at the department level.  In hopes to provide the needed information ( am not able to upload a pbix file) I have a printscreen of my model and what I am expecting in excel.

 Any help would be much appreciated!  I am not sure how to move forward.

Thank you,

Anya

[ShortyAO]

I was able to resolve with the various responses I received.  Thank you all that provided their input.  I ended up creating a table by using the following measure = 

CrossJoin(SUMMARIZECOLUMNS(Start_End_Dates[Employee Number],Start_End_Dates[Employee Name (Last Suffix, First MI)],Start_End_Dates[Cost Center],Start_End_Dates[Adj Hire Date],Start_End_Dates[Termination Date]),Calender).  Within the table I created a column to populate the available working hours through an IF STMT.  This allowed me to be able to then created my measure to sum and be able to use the needed dates for my visuals.  I am sure there probably was a better or more efficent manner of resolving issue.  Being fairly new to Power Bi, this was the solution I was able to arrive to.  Thanks again everyone for your input!  

[Ashish_Mathur]

Hi,

Share the download link of your PBI file and also the MS Excel file (which you have shared in your screenshot).

[ShortyAO]

Unfortunetly, I don't have the option to upload with my profile.

[Ashish_Mathur]

Hi,

Upload the file to Google Drive and share the download link here.

[HoangHugo]

Hi, I think there are no active relation between table Calendar and Start End Date, so your CALCULATE is no true.

Click, one of two relation (x), and "Active" it

Any way, there are two relation from Start End Date to Calendar, so you should decide what date filed you want to apply filter.

[ShortyAO]

I have updated activated the relationship between Start End Date to Calender by Termination date.

Updated measure:  NEW Hours Available = CALCULATE(sum(Calender[2080 Per Day]),FILTER(Calender,Calender[Date]<=LASTDATE(Start_End_Dates[Termination Date])))

[amitchandak]

@ShortyAO , check if one of the two ways can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...