Employee retention data

Hi RichOB,

You need First to identify the duplicated NI_Numbers using DAX:

NI_Count = CALCULATE(COUNTROWS('Employees'), ALLEXCEPT('Employees', 'Employees'[NI_Number]))

 Note: After that you should filter your visual NI_Count > 1 (only employees who returned will appear in your visual or the table you filtered)

After that calculate the Days_Between by adding Calculated column:

Days_Between = 
VAR PrevDateLeft = 
    CALCULATE(
        MAX('Employees'[Date_Left]),
        FILTER(
            'Employees',
            'Employees'[NI_Number] = EARLIER('Employees'[NI_Number]) &&
            'Employees'[Date_Joined] < EARLIER('Employees'[Date_Joined])
        )
    )
RETURN
IF(NOT(ISBLANK(PrevDateLeft)), DATEDIFF(PrevDateLeft, 'Employees'[Date_Joined], DAY), BLANK())

 
Let me know if you find it useful 🙂 .

Hi RichOB 

You can write a column as follows:

RichOB 

Hi, in Power Query it would be (adjust the hard-coded column names if you need to), 

let
    Source = yourtable,
    #"Changed Type" = Table.TransformColumnTypes(yourtable,{{"NI_Number", type text}}),
    GroupedRows1 = Table.Group(#"Changed Type", {"NI_Number"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Data", each _, type table [NI_Number=nullable text, Name=nullable text, Title=nullable text, Date_Joined=date, Date_Left=date, Days=nullable number]}}),
    FilteredRows = Table.SelectRows(GroupedRows1, each [Count] > 1),
    Expanded = Table.ExpandTableColumn( FilteredRows, "Data", {"Name", "Title", "Date_Joined", "Date_Left", "Days"}),
    Sorted = Table.Sort(Expanded, {{"NI_Number", Order.Ascending}, {"Date_Joined", Order.Ascending}}),
    GroupRows2 = Table.Group(Sorted, {"NI_Number"}, {
        {"AllData", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table [NI_Number=nullable text, Name=nullable text, Title=nullable text, Date_Joined=date, Date_Left=date, Days=nullable number, Index=Int64.Type]}
    }),
    #"Expanded AllData" = Table.ExpandTableColumn(GroupRows2, "AllData", {"Name", "Title", "Date_Joined", "Date_Left", "Days", "Index"}, {"Name", "Title", "Date_Joined", "Date_Left", "Days", "Index"}),
    PrevStep = Table.Group(#"Expanded AllData" , {"NI_Number"}, {
        {"WithPrev", each 
            let 
                t = _,
                shifted = Table.AddColumn(t, "Prev_DateLeft", each try t[Date_Left]{[Index]-2} otherwise null)
            in shifted, 
            type table [NI_Number=nullable text, Name=nullable text, Title=nullable text, Date_Joined=date, Date_Left=date, Days=nullable number, Index=Int64.Type, Prev_DateLeft=nullable date]}
    }),
    Expanded3 = Table.ExpandTableColumn(PrevStep, "WithPrev", {"Name", "Title", "Date_Joined", "Date_Left", "Days", "Index", "Prev_DateLeft"}),
    AddedDaysBetween = Table.AddColumn(Expanded3, "DaysBetween", each if [Prev_DateLeft] <> null then Duration.Days([Date_Joined] - [Prev_DateLeft]) else null, Int64.Type),
    FinalSorted = Table.Sort(AddedDaysBetween, {{"NI_Number", Order.Ascending}, {"Date_Joined", Order.Ascending}})
in
    FinalSorted

Hi RichOB ,

Thanks to all the Ahmed-Elfeel , MasonMA  & Selva-Salimi  for providing solutions.

Did you get a chance to look into their suggestions? If any of the solutions helped, that’s great! If you run into any issues or need further clarification, please let us know happy to help.

Thanks,
Akhil.

Hi RichOB ,


Just checking in were you able to try the suggestions from super users. If any of the solutions worked, that’s great. If you’re still facing issues or need further clarification, we’re happy to help.

Thanks,
Akhil.