Match two different table datas by closest date?

[AleksiK]

So in this example i have two tables of data: One that contains Net Promoter Score from my customers. The "rating" they have gave of my services. Example of data looks like this:

Date	Customer-ID	Score
1.1.2020	1234	10
1.2.2020	4321	0
1.3.2020	1578	8
1.4.2020	1578	9
1.5.2020	4321	10
1.6.2020	1234	0

Second table of data contains ALL occurenses of the services customer has had. And information about which employee has served this customer etc. Example looks like this:

Date	Customer-ID	Employee
29.12.2019	1234	Employee 1
31.1.2020	4321	Employee 1
10.2.2020	1578	Employee 2
31.3.2020	1578	Employee 2
15.4.2020	4321	Employee 3
25.5.2020	1234	Employee 3
1.6.2020	1234	Employee 1
23.7.2020	4321	Employee 1
26.8.2020	1578	Employee 2
19.9.2020	1578	Employee 2
10.10.2020	4321	Employee 3
11.11.2020	1234	Employee 3

So, I want to calculate the net promoter score of specific employee. The problem is I need to match 1. Customer ID + 2. The Closest Date. By default Power BI matches the first match it founds. Is it possible to do this matching by closest date AND customer ID, so I can calculate

[v-deddai1-msft · Accepted solution]

Hi @AleksiK ,

You can create a calculated column for it:

customer = 
VAR _datediff =
    MINX (
        FILTER ( ALL ( Table2 ), Table2[Customer-ID] = EARLIER ( Table1[Customer-ID] ) ),
        ABS ( Table2[Date] - EARLIER ( Table1[Date] ) )
    )
VAR a =
    CALCULATE (
        MAX ( Table2[Employee] ),
        FILTER ( Table2, Table2[Date] = EARLIER ( Table1[Date] ) + _datediff )
    )
VAR b =
    CALCULATE (
        MAX ( Table2[Employee] ),
        FILTER ( Table2, Table2[Date] = EARLIER ( Table1[Date] ) - _datediff )
    )
RETURN
    IF ( ISBLANK ( a ), b, a )

For more details, please refer to the pbix file:  https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdKbZUWwZD1BsDAiJT...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[lbendlin]

Please be more specific.  Closest date in which direction? Before or after? what if there are ties between employees?