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

[SEMattis]

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

End Date	Employee #	Cost Center	Reason for Leaving
2021-06-30	2865423135654	2241	New Job - Externally
2021-01-08	5646546546546	3389	Personal Reasons
2020-11-01	2865403215789	8800	Dismissed

Table 2 - Org Data

Org Unit	Org Subunit	Team	Cost Center	Snapshot Date
Contoso	WebFrontEnd	WordWideWebbers	2241	2021-07-31
Contoso	WebDevOps Engineering	DevOlution	3389	2020-12-01
Contoso	WeDoStuff	Coffee	8800	2021-02-28

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!

[SEMattis]

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

End Date	Employee #	Cost Center	Reason for Leaving
2021-06-30	2865423135654	2241	New Job - Externally
2021-01-08	5646546546546	3389	Personal Reasons
2020-11-01	2865403215789	8800	Dismissed

Table 2 - Org Data

Org Unit	Org Subunit	Team	Cost Center	Snapshot Date
Contoso	WebFrontEnd	WordWideWebbers	2241	2021-07-31
Contoso	WebDevOps Engineering	DevOlution	3389	2020-12-01
Contoso	WeDoStuff	Coffee	8800	2021-02-28

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!

[Anonymous]

This should be performed in Power Query, not in DAX, for maximum performance. By the way... there's no entry in the Cost Center column in the first table.

[SEMattis]

@Anonymous , thanks for highlighting the missing data. Ok, how would I go about looking this up in PQ instead of dax?

[Anonymous]

// Leaver Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbQNzDUNzIwMlTSUQJh52BDpVidaCUjC30DI5iEEVjCCCwB0mEMkzAGSxgrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"End Date" = _t, #"Employee #" = _t, #"Cost Center" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"End Date", type date}, {"Employee #", Int64.Type}, {"Cost Center", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Cost Center"}, #"Org Data", {"Cost Center"}, "Org Data", JoinKind.LeftOuter),
    #"Expanded Org Data" = Table.ExpandTableColumn(#"Merged Queries", "Org Data", {"Org Unit", "Org Subunit", "Team", "Snapshot Date"}, {"Org Unit", "Org Subunit", "Team", "Snapshot Date"}),
    #"Added NumOfDaysDiff" = Table.AddColumn(#"Expanded Org Data", "NumOfDaysDiff", 
        each Number.Abs( Duration.TotalDays( [End Date] - [Snapshot Date] ) )
    ),
    #"Added MinNumOfDaysDiff" = Table.AddColumn(#"Added NumOfDaysDiff", "MinNumOfDaysDiffByEmp", 
        each 
        let
            DaysDiffColumn = Table.SelectRows(
                #"Added NumOfDaysDiff",
                (r) => r[#"Employee #"] = [#"Employee #"]
            )[NumOfDaysDiff],
            MinDiff = List.Min( DaysDiffColumn )
        in
            MinDiff
    ),
    #"Added Comparison" = Table.AddColumn(#"Added MinNumOfDaysDiff", "NumOfDaysIsMinimum", each [NumOfDaysDiff] = [MinNumOfDaysDiffByEmp]),
    #"Removed FALSE" = Table.SelectRows(#"Added Comparison", each ([NumOfDaysIsMinimum] = true)),
    #"Removed Helper Columns" = Table.RemoveColumns(#"Removed FALSE",{"Snapshot Date", "NumOfDaysDiff", "MinNumOfDaysDiffByEmp", "NumOfDaysIsMinimum"})
in
    #"Removed Helper Columns"

// Org Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5BC4MgGMbxrzI8B+lTh91XXTtEeIguI4UgNJrt8y/rrWYE8qrgD/9Nw8r6IVjEpHoXkzUuN912l3LdX5WfXMTLAodgbfRnukx9y/Gz3vxxmF1vDUEQxA3MbOVmrbeXVmulCCXLxPOKcCkEFeIsBBkemr0QYSGCwuTms70QZyGOQo9SQu0P", 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", type text}, {"Snapshot Date", type date}})
in
    #"Changed Type"

The code above does what you want but it uses the assumption that there will never be 2 different snapshot dates

(for the same Cost Center) that will be equidistant from the End Date of an employee. So, for instance, if there were an employee with an End Date, say, of 1 jan 2020 and there were 2 entries for his cost center, 2 jan 2020 and 31 dec 2019, then the code will return 2 rows for the employee. You'll need additional logic to handle this situation if it can happen. Also, the code assumes that for each employee there'll be at least one entry in the Org Data table (based on their Cost Center). If this is not the case, you might get an error and you'll have to modify the code slightly to handle such a case.

[SEMattis]

Wow, this certainly looks complex for such a small case. Is there no way of utilizing DAX instead of PQ to solve the problem? 

[Anonymous]

No idea why you'd prefer DAX to PQ since in PQ it's easier (contrary to what you think) and the table gets optimized (it gets the best compression, thus DAX is faster). Calculated columns in DAX should always be the very last resort. You don't have this need here. Just copy my code and paste it into PQ. Just like that. Paste it into the left-hand side (right-click) where you see all the tables' names, function names and parameter names.

[v-yingjl]

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.

[SEMattis]

@v-yingjl , in terms of performance (As I will have a couple of hundred of thousands of rows in my data set) how will this impact the query itself? Is there no way of building a DAX measure to solve this problem instead?

[v-yingjl · Accepted solution]

Hi @SEMattis ,

If you want to create a DAX measure, you need to create a relationship between these tables based on the cost center field.

Create a date measure like this:

_Date =
MIN ( 'Leaver Data-DAX'[End Date] )
    + DATEDIFF (
        SELECTEDVALUE ( 'Leaver Data-DAX'[End Date] ),
        CALCULATE (
            MIN ( 'Org Data-DAX'[Snapshot Date] ),
            ALLEXCEPT ( 'Org Data-DAX', 'Org Data-DAX'[Cost Center] )
        ),
        DAY
    )

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.