Find Trained Employees who Left – DAX Challenge

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
2. When you post your answer in the comments, also do post the relationship diagram of your model
3. And yeah.. should you need a calendar / date table, you can draw one for yourself or take it from here

DOWNLOAD THE EXCEL FILE WITH DATA

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

Hungry for more of these ?

1. Find days before the Earliest Date
2. Find Unique Dates – Challenge
3. Separate Employee Names from Managers
4. Separate the values of the column