Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Match two different table datas by closest date?

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:

 

DateCustomer-IDScore
1.1.2020123410
1.2.202043210
1.3.202015788
1.4.202015789
1.5.2020432110
1.6.202012340

 

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:

 

DateCustomer-IDEmployee
29.12.20191234Employee 1
31.1.20204321Employee 1
10.2.20201578Employee 2
31.3.20201578Employee 2
15.4.20204321Employee 3
25.5.20201234Employee 3
1.6.20201234Employee 1
23.7.20204321Employee 1
26.8.20201578Employee 2
19.9.20201578Employee 2
10.10.20204321Employee 3
11.11.20201234Employee 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

Need Help
Tutorial Requests
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    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/EdKbZUWwZD1BsDAiJTryhL8BwwTWBbx1BS5t_GQ4TkJ7kg?e=FhazVf

     

    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

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

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

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    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/EdKbZUWwZD1BsDAiJTryhL8BwwTWBbx1BS5t_GQ4TkJ7kg?e=FhazVf

     

    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