Dynamic Date Filter needed on Table with Two Date Rows: Retention analysis calculations

[Brianoreilly]

Hi Folks, 

I currently have an issue calculation a dynamic employee retention based on our HR System Data. 

I am hoping to devise a dynamic dashbaord, where HR can use a slicer connected to a date table that can recalculate based on the time frame selected. 

My Problem is, that I cannot relate the 'Date Table' to one of the columns as I require the use of two dates in the calculations. 

I currently require 3 calculations:

As Follows:

Formula 1 Required: Count of "Active Employees" before or equal to the Max Date in Date Table Filter
Formula 2 Required: Count of "Leavers" before or equal to the Max Date in Date Table Filter
Formula 3 Required: Retention: (Total Number of Leavers within the Max and Min Dates) / (Average Active Headcount per month within the Max and Min Dates)

Any help with trying to devise these measures would be greatly appreciated, as I am at a loss. 

Thanks, 

Brian.

Table 1: Employee Data		Table 2: Date Table
Employee ID	Latest Start Date	Date Left		01/01/1900
8008769	18-Feb-17			02/01/1900
4365214	11-Dec-16			03/01/1900
4367562	17-Dec-16			04/01/1900
4364898	13-Dec-16			05/01/1900
4363486	11-Dec-16			06/01/1900
4369189	19-Dec-16			07/01/1900
4371464	11-Jan-17			08/01/1900
4371447	11-Jan-17			09/01/1900
4362807	11-Dec-16			10/01/1900
4363791	14-Dec-16			11/01/1900
4362314	01-Dec-16			12/01/1900
4370576	03-Jan-17			13/01/1900
8023650	20-Aug-17			14/01/1900
1334328	03-Dec-09	28-Jul-17		15/01/1900
1336947	04-Dec-09	01-Jul-17		16/01/1900
1371470	29-Nov-09	24-Feb-17		17/01/1900
1248268	18-Jul-09	22-Apr-17		18/01/1900
1257804	21-Aug-09	20-Apr-17		19/01/1900
1781872	16-Nov-11	30-Jan-17		….

[LivioLanzo]

Hi @Brianoreilly

download my file: https://1drv.ms/u/s!AiiWkkwHZChHjxzyDJFSK_jjEjTs

let me know if something is not clear

[Brianoreilly]

Hi @LivioLanzo, 

Appreciate the help.

Sorry but this doesn't do the trick for me, as I have thousands of records. 

I need something a bit more robust.

What I need are calculated measures as such.

Formula 1:

This counts the number of active employees per time period of when they started. 

Active Employee Count Per Month (Non Cumulative) = 
VAR currentDate = MAX('Date'[Date])
RETURN
    CALCULATE (
        COUNTA ('Employee Data'[Employee ID] ),USERELATIONSHIP('Date'[Date],'Employee Data'[Latest Start Date]),
        FILTER (
            'Employee Data',
            ( 'Employee Data'[Latest Start Date] <= currentDate
              )&& 'Employee Data'[Date Left] = BLANK() 
        )
    )

I then cumulate this by 

Active Employee Count Per Month (Cumulative) = 
CALCULATE(
	[Active Employee Count Per Month (Non Cumulative)],
	FILTER(
		ALLSELECTED('Date'[Date]),
		ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
	)
)

 These two formulas work correctly. 

I can do the same two formulas for leavers and change user relationship on date to leave date:

To get leavers per month and cumulative leavers total. 

I would survive if I could figure out one more formula. 

This formula would be the number of leavers between a filtered from date and a filtered to date. 

Ie. in the screenshot below:

The first graph shows the leavers in the quarter.

The second graph, shows the cumulative leavers, but starts on the very first date ever. I need this to cumulate from the 1st 2017 in the example attached. 

Code for black value in graph two:

Inactive Employee Count Per Month (Cumulative)2 = 
CALCULATE(
	[Inactive Employee Count Per Month (Non Cumulative)],
	FILTER(
		ALLSELECTED('Date'[Date]),
		ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
	)
)

Really appreciate any help. 

Thanks, 

Brian. 

[LivioLanzo]

hI @Brianoreilly

what do you mean by more robust? are the measure in my report slow? if so, there's an additional step that can be taken to make them faster.

You are going to need to modify your data a little, either within Power Query or via a calculated table, if you want to have a fast report. The way the data is at the moment with a start date and end date is not optimal and you need to build complicated DAX to make it work, and more complicated is the DAX slower is the report

[Brianoreilly]

Hi @LivioLanzo, 

The ActiveEmployees table is too large it looks unfortunately. 

The method you advise would work brilliant, if my dataset wasn't so cumbersome. 

Thanks for the advise. 

I need an alternative, that I have nearly cracked. 

Just need to get one more formula working. 

Thanks,

 Regards,

Brian. 

[LivioLanzo]

There is a way to further aggregate the activeEmployees table so it becomes much smaller than it is in the file I sent. If that's something you would consider let me know and I upload another file