Extrapolation of Data as per use case

[Rohitb]

Hello

I have the below dataset at my disposal.

EmloyeeId	DOJ	DOL	IsContinuityNeeded	ContinuedFor
1	7/28/2022	9/25/2023	1
2	9/23/2023	11/30/2023	1
3	10/2/2023
4	5/30/2023	9/15/2023	1
5	2/25/2023	9/10/2023
6	12/15/2023			4
7	11/5/2023			2
8	12/6/2023

I need to extrapolate/populate the data as per below

Month	Employee ID
23-Sep	1
23-Sep	2
23-Sep	4
23-Sep	5
23-Oct	1
23-Oct	2
23-Oct	3
23-Oct	4
23-Oct	5
23-Nov	1
23-Nov	2
23-Nov	3
23-Nov	4
23-Dec	1
23-Dec	3
23-Dec	6
23-Dec	7
23-Dec	8

Here the requriment is :

Employees with No Departure Date (DOL):
For employees without a departure date (DOL), their records should be populated from the month they joined (DOJ) until the maximum month for which we have data. (Example EmployeeID : 3 & 8 )

Employees with Departure Dates and No Continuity Requirement (IsContinuityNeeded is blank):
For employees with departure dates (DOL) and no continuity requirement (IsContinuityNeeded is blank), their records should span from the month of thier date of joining (DOJ) until their departure month (DOL date month). (Example EmployeeID : 5)

Employees with Departure Dates and Continuity Requirement (IsContinuityNeeded is 1):
a. If there's no replacement employee (ContinuedFor column is blank), the records should cover the period from the employee's month of joining (DOJ) until the maximum month for which we have data. (Example EmployeeID : 1 )
b. If a replacement employee exists, and they joined before the departing employee (DOJ of replacement < DOL of departed), exclude the records of the new employee till the departure month of date of old employee (DOL). (Example EmployeeID : 2/7 )
c. If a replacement employee exists, and they joined after the departing employee left (DOJ of replacement > DOL of departed), include record of the departing employee for the months between their departure date (DOL) and the replacement employee's date of joining (DOJ). (Example EmployeeID : 6/4 )

Can someone help me with this ?

[lbendlin · Accepted solution]

MEASURE =
VAR d =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR e =
    EVALUATEANDLOG (
        ADDCOLUMNS (
            Employees,
            "Replacement Start",
                VAR em = [EmloyeeId]
                RETURN
                    MINX ( FILTER ( ALL ( Employees ), [ContinuedFor] = em ), [DOJ] ) -- start date for replacement employee
                    ,
            "New DOJ",
                IF (
                    ISBLANK ( [ContinuedFor] ),
                    [DOJ],
                    VAR pem = [ContinuedFor]
                    RETURN
                        MAX (
                            [DOJ],
                            MAXX ( FILTER ( ALL ( Employees ), [EmloyeeId] = pem ), [DOL] ) + 1
                        )
                )
        )
    ) -- adjusted start date for replacing employee
VAR a =
    ADDCOLUMNS (
        e,
        "in",
            SWITCH (
                TRUE (),
                d >= [New DOJ]
                    && ISBLANK ( [DOL] ), 1,
                -- no end date
                d >= [New DOJ]
                    && d <= [DOL]
                    && ISBLANK ( [IsContinuityNeeded] ), 1,
                -- end date without continuity
                d >= [New DOJ]
                    && ISBLANK ( [Replacement Start] ), 1,
                -- end date, continuity needed but no replacement
                d >= [New DOJ]
                    && [Replacement Start] < [DOL]
                    && d <= [DOL], 1,
                --replacement starts before DOL - continue until DOL
                d >= [New DOJ]
                    && [Replacement Start] >= [DOL]
                    && d < [Replacement Start], 1 --replacement starts after DOL - continue until Replacement
            )
    )
RETURN
    SUMX ( a, [in] )

This is not something you should do in Power BI. Too complex.

See attached.