Recently, while working on a project I came across an interesting problem.

The Scenario – Let’s say a company is pretty aggressive in conducting training interventions for it’s employees. On the flip side, their manager is always worried about “what if we train and they leave?”

My job was to find out the trained employees leaving the company

### Consider this Emp Data..

• Each row is a single employee
• With standard columns like- E code, Name, Dept, DOJ, Last Day etc..
• The Last Day will only exist if the employee has left the company else blank

### Now consider this Training Data..

• One employee can undergo many training programs
• No employee will undergo a training after his / her Last Day

### The Problem

The manager needs to find, how many people left the company within 3 months of the training date

A few more things..

• The manager would like to filter the result by Month, Qtr or Year
• The result can also be filtered by any Emp Table dimension (dept, business function, band etc..)

### Rules

1. Try to come up with a single measure. Use NO table/helper columns
3. And yeah.. should you need a calendar / date table, you can draw one for yourself or take it from here

### Update (21st July 2019) – Here is my answer

```Trained and Left =
CALCULATE (
COUNTROWS ( EmpData ),
FILTER (
'EmpData',
VAR LastDay =
CALCULATE ( MAX ( EmpData[Last Day] ), ALLEXCEPT ( EMPdata, EmpData[E.Code] ) )
RETURN
IF (
LastDay <> BLANK (),
CALCULATE (
MAXX (
CALCULATETABLE (
RELATEDTABLE ( Training ),
DATESBETWEEN ( Cal[Date], EDATE ( LastDay, -3 ), LastDay )
),
Training[Training Date]
)
)
)
),
USERELATIONSHIP ( Cal[Date], EmpData[Last Day] )
)```

Download the Solved Power BI File From Below – I am pretty sure my code can be optimized further. Feel free to nit pick in the comments