Deal with data that not based on snapshot

[selected_]

I'm trying to analyzing Turn Over rate for employees in a HR data, in one table named employments I have information about employeeID, HireDate and TerminationDate and second table is a Calender table that I have relationship with. 

I wanna see number of terminated employees during measured period (for example 1st of January – 31st of December for certin year) divided with  (Number of active empolyees at the startdate of the period measured (for example 1st of January) plus number of active employees at the enddate of period measured (for example 31st of December) divided by 2).

Example – terminated employees 100, number of employees 1st of January 1000, number of employees 31st of December 1200

100/(1000+1200/2) = 9

I have tried with so many measured but I can't get right figures, I asked one about that and he said it's due logically there is no relationship between these tables since data is not based on snapshot hence try to handle to filter at measure level. 

I don't understand because I have relationship between Employement and Calender table, which is HireDate column with Date column in the Calender table. 

Please help!

[selected_]

I still don't get it, how can I slicer on date in the page if I remove the relationship?

I created the measure but it gives me blank

Turnover_in_selected_date_range =

VAR start_date = MIN(Calendar[Date])

VAR end_date = MAX(Calendar[Date])

VAR StartEmployees = CALCULATE(SUM('employments'[employeeId]), 'employments'[jobInfo.startDate] = start_date)

VAR EndEmployees = CALCULATE(SUM('employments'[employeeId]), 'employments'[lastDayWorked] = end_date)

VAR AvgEmployees = (StartEmployees + EndEmployees) / 2

RETURN

DIVIDE(SUM('employments'[terminationDate]), AvgEmployees)

[kpost]

I'll dissect just one part of your formula that you wrote.
VAR StartEmployees = CALCULATE(SUM('employments'[employeeId]), 'employments'[jobInfo.startDate] = start_date)

In this formula, you are summing the employee ID for some reason.  So if an employee ID was 52, and another one was 60, you'd be saying that there were (52+60) = 112 employees.  This is probably not what you intend to do.  I think that you want each employee to count as 1 employee.

You're also saying only include employees where the jobInfo.startDate is EQUAL TO the start_date of January 1.  Therefore if nobody was hired exactly on January 1, you're going to get exactly zero employees. This is probably not what you intend to do.

///////////////////////////////////////

Here's how to fix:


Add a new column to your employments table:

Single_employee = 1

Then modify the measure to be like this:

Turnover_in_selected_date_range =

VAR start_date = MIN(Calendar[Date])

VAR end_date = MAX(Calendar[Date])

VAR StartEmployees =
CALCULATE(COALESCE(SUM('employments'[Single_employee]), 0), 
'employments'[jobInfo.startDate] <= start_date,  
'employments'[lastDayWorked] >= start_date ||  'employments'[lastDayWorked] = BLANK())

RETURN

StartEmployees
/////////////////////////////////////////////////
This will give you the number of employees that were employed as of the start_date selected in your slicer.


//Mediocre Power BI Advice but it's free//

[selected_]

Sorry but did you saw how I wanna calculate? 

I wanna see number of terminated employees during measured period (for example 1st of January – 31st of December for certin year) divided with  (Number of active empolyees at the startdate of the period measured (for example 1st of January) plus number of active employees at the enddate of period measured (for example 31st of December)  and then divided by 2.

Example – terminated employees 100, number of employees 1st of January 1000, number of employees 31st of December 1200

100/(1000+1200/2) = 9

how should it look like based on the measure?

[kpost]

Here is Power BI file where I have fully solved your problem

https://files.catbox.moe/xco4ku.pbix

[kpost]

If I understand you correctly, your relationship is between Calendar[Date] and Employments[Hire Date]

This means that if you use the Calendar Date as a slicer value and you select Jan 1 through Dec 31 for a certain year, your calculations will include only employees (rows of data) in your calculations where the Hire Date is between Jan 1 and Dec 31, regardless of when they were fired.

So let's say you want to Figure out who was an active employee on January 1.  You'd probably do that by looking at everybody with a hire date on or before January 1, and a fire date that is either blank, or on or after January 1.

The problem is that since you have selected January 1 in your slicer, you are by definition excluding everybody with a hire date before January 1.

You'd honestly be better off just getting rid of the relationship between your calendar table and your Employment table, and creating a measure like this where you use the starting and ending date as variables and calculate your turnover that way.  Otherwise it seriously complicates your DAX measure because you have to keep telling it to both use the Calendar Date Slicer's max and min values, but ignore the fact that there is a relationship between the two tables.

Turnover_in_selected_date_range = 
var start_date = MIN(Calendar[Date])
var end_date = MAX(Calendar[Date])

return
<Put equation here, where you use start and end date as filter conditions in CALCULATE statements to figure out your turnover>


//Mediocre Power BI Advice, but it's free//