Lookup historical value based on nearest snapshot date compared to end date

Hi SEMattis ,

You can achieve it directly in power query by merging and filtering tables, try this query:

Ora Data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY4xC8IwEIX/imRu4JIIxrmtq4NDh9Kl5FICcidJ6+/3ouBgF6d774N7742japlWLqwaNeB8yeJ6CtVxDkMKKHTGXIRYezT1gDUaTtoZNTU//x0+r49y6GlJhJgTLcIrvG9rYhLjnD9/QkAbq2Ef0vFt3WIU3XKMiCK8B/g2W239vvn/5f69fHoB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Org Unit" = _t, #"Org Subunit" = _t, Team = _t, #"Cost Center" = _t, #"Snapshot Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Org Unit", type text}, {"Org Subunit", type text}, {"Team", type text}, {"Cost Center", Int64.Type}, {"Snapshot Date", type date}})
in
    #"Changed Type"

Leaver Data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY7LCsIwEEV/Jcy6gXkkMe7tpgsRtyWLilkUqoVGsP69EzFw4cyFOcyMIzAyWQxWEDrgGLxjIfHK2tmR4pzfZlhvxpp+f+XtOS3LB1LXZE3ULR+cWv9oF4lHxSVvZVXFXPOkQ2kiWqpuu4rC5A8/I0asz5zm8phLyXdI6Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"End Date" = _t, #"Employee #" = _t, #"Cost Center" = _t, #"Reason for Leaving" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"End Date", type date}, {"Employee #", Int64.Type}, {"Cost Center", Int64.Type}, {"Reason for Leaving", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Cost Center"}, #"Org Data", {"Cost Center"}, "Org Data", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.Min([Org Data],"Snapshot Date")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Org Unit", "Org Subunit", "Team", "Snapshot Date"}, {"Custom.Org Unit", "Custom.Org Subunit", "Custom.Team", "Custom.Snapshot Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Org Data"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

I'm working on developing a Power BI solution for co-worker turnover which may seem simple at first. However, since I'm working in an organisation that keeps on changing labels that I need for mapping to the organisation I need to take snapshots of the organisational data and link the snapshot date to the end date which I have been able to do in a good way.

To exemplify, my solution consists of two tables. Table 1, contains leaver data, Table 2, contains organisational data in the following manner:

Table 1 - Leaver Data

Table 2 - Org Data

Wished output

What I effectively want is to write a DAX expression to lookup the Org Unit, Org Subunit and Team (Send the values to table 1) of the Cost Center matching with the person record's cost center for the snapshot date closest/nearest the end date of the individual. Hope this makes sense...

Thanks!