Get End Date by Next Action Date - Get Continuous Date Range by Category

[wskinner]

Hello,

I have a question about generating an end date by only using effective dates. I can easily do this by grouping, and I have also seen how to make the 0 Index column and 1 Index column to merge a table with itself.
However, I have a specific issue causing me to have overlapping dates. It has to do with a position that changes it's manager back to a previous position that it already had.

Example:
Position "ABC123" reports to manager "BBB111" from 25-Feb-2013 until it was changed to report to manager “CCC222” on 01-Jan-2018. The position "ABC123" will continue reporting to "CCC222" from 01-Jan-2018 until 07-Sept-2021 when the reports to manager is changed back to "BBB111" where it stays reporting until currently.

Position ID	Reports To	Effective Date	Effective End Date
ABC123	BBB111	25-Feb-2013	31-Dec-2017
ABC123	CCC222	01-Jan-2018	06-Sept-2021
ABC123	BBB111	07-Sept-2021

A couple of issues with the data is that I’m not able to pull only when the manager changes. I have to pull all the dates for any type of changes to the position. So there are dates involved that I don’t care about. Like a position may have 3 rows and dates of various changes, but it still reports to the same manager before changing to another manager. I only want the range that the position reports to a manager.

I have a list of employee positions and the history of changes to the postion as well as the manager position it is reporting to. I want to get the date ranges a position is active and which manager position it is reporting to. I am trying to have a reference for a historical organization chart.

The ultimate purpose of this is so I can reference this table using a date, and it will tell me which positions were active that date and who did it report to at the time.

Here is a photo of what my source data looks like and what I want it to end up looking like. I will attach a file that has the data.

Source and Final Output

Here is a smaller sample data in a table for reference.

Position ID	Reports To Position	Effective Date
123456	831502	2/25/2013
123456	831502	4/6/2015
123456	831502	5/8/2015
123456	829162	1/1/2018
123456	829162	5/11/2020
123456	831502	9/7/2021
123456	831502	12/18/2023
222333	832612	6/25/2013
222333	832612	4/19/2015
222333	832612	4/21/2015

What the table should look like:

Position ID	Reports To Position	Effective Date	Effective End Date
123456	831502	2/25/2013	12/31/2017
123456	829162	1/1/2018	9/6/2021
123456	831502	9/7/2021	12/18/2023
222333	832612	6/25/2013	4/21/2015

However I could do this in Power BI Desktop or in Excel using M Power Query.

Thanks for the assistance

[wskinner]

Thank you @Ahmedx ! I was easily able to duplicate this in PowerBI and Excel. This is incredibly helpful!

[dufoq3]

Hi @wskinner, another solution:

Result

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevValue"} 
                    else              {col & "_NextValue"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,

    fnDates = 
        (myTable as table)=>
        [
            // _Detail = GroupedRows{[#"Position ID"=123456]}[All],
            _Detail = myTable,
            _GroupedRowsLocal = Table.Group(_Detail, {"Position ID", "Reports To Position"}, {{"All", each _, type table}, {"Min", each List.Min([Effective Date]), type date}, {"Max", each List.Max([Effective Date]), type date}}, GroupKind.Local),
            _fnShiftMin = fnShift(_GroupedRowsLocal, "Min", -1, null, type date),
            _Ad_EffectiveEndDate = Table.AddColumn(_fnShiftMin, "Effective End Date", each if [Min_NextValue] <> null then Date.AddDays([Min_NextValue], -1) else [Max], type date),
            _RenamedColumn = Table.RenameColumns(_Ad_EffectiveEndDate,{{"Min", "Position#(tab)Effective Date"}}),
            _RemovedOtherColumns = Table.SelectColumns(_RenamedColumn,{"Position ID", "Position#(tab)Effective Date", "Effective End Date"})
        ][_RemovedOtherColumns],

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc/RCcQwDAPQXfJdUCTHvmSW0v3XaN07KOWSP8MTtrzvhbLmUbbSjV51DYIcqrRybDNviGRfsKPPWIORTDC5L9jBdNXF9oFPMhdMgXle3/KSzOwOKJiBeD337w0cT/2Ziz8/Tg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Position ID" = _t, #"Reports To Position" = _t, #"Effective Date" = _t]),
    ChangedTypeUS = Table.TransformColumnTypes(Source,{{"Position ID", Int64.Type}, {"Reports To Position", Int64.Type}, {"Effective Date", type date}}, "en-US"),
    GroupedRows = Table.Group(ChangedTypeUS, {"Position ID"}, {{"All", fnDates, type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZS9bsMgFIVfxfKcUi4/BuZ06dQqaqcoA0qsxFJlRzbJ0KcvEONS1TF2JA8gnQ8u59zr7TYHQhkv8lUuKXBM7IJwhAkiGKjdbMqnt/ao6+pbm6qpnzdlZ9rL3lzaMt+ttnkM4gJh5kBuN+9NVzkiWzf1tWw7u/wPSIR5AD4q81Vm65Ouj79HEwWFVwKyn1VKX9O5aU2XmeaeHOB2MMFxJS/a6OzzfNBmpHaBsHIEzCVAInA2ETpFMMY4d8+T7FF72VJ7h5twyl5KCwj2SqcsJu0NchB9JSJpVriA9oHAWClSSqWU02MOTDxkUrioQIAdyCZfEsnneRoKs0751L1Tk0+PCN9Zf80aiy0KY+j10dgi5a0YmSomJnByMAghlFI/UD23OI8AgooMXgISGEvmtdZ7U11toQdPAIAQzlgpHh2vASR9Tuye7WLpIIrZ/7mgpH0+6fYqgHN/tuq7JTmLA2GdFbO6JiJSXbP7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Position ID" = _t, #"Reports to Position" = _t, #"Effective Date" = _t, #"Workforce Action Reason Description" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Position ID", Int64.Type}, {"Reports to Position", Int64.Type}, {"Effective Date", type date}, {"Workforce Action Reason Description", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Position ID"}),
    #"Grouped Rows" = 
            Table.Group(#"Filled Down", {"Position ID"},
                        {{"Count", (x)=>
                               Table.AddColumn( Table.AddIndexColumn(  x,"Index",0,1),"nextDate", each [
                                a=List.Buffer( x[Effective Date]&{Date.AddDays( List.Max( x[Effective Date]),1)}),
                                b=   List.Range( a,[Index]+1,1){0},
                                c = Date.AddDays(b,-1) ][c]    
)
 

}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Position ID", "Reports to Position", "Effective Date", "Workforce Action Reason Description", "Index", "nextDate"}, {"Position ID", "Reports to Position", "Effective Date", "Workforce Action Reason Description", "Index", "nextDate"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Count", {"Position ID", "Reports to Position"}, {{"MinDate", each List.Min([Effective Date]), type nullable date}, {"MaxDate", each List.Max([nextDate]), type nullable date}},GroupKind.Local)
in
    #"Grouped Rows1"