Forum Discussion

clteh9's avatar
clteh9
Helper I
3 years ago
Solved

match record with nearest datetime

Hi, 

 

I have 2 tables and would like to match the record with the nearest date. 

 

table 1:

 

table 2:

 

expected result:

 

thanks

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    clteh9 

    pls try this 

    Column = 
VAR _l=maxx(FILTER('Table 2','Table'[item]='Table 2'[item]&&'Table'[end time]>='Table 2'[last_dt]),'Table 2'[last_dt])
VAR _n=minx(FILTER('Table 2','Table'[item]='Table 2'[item]&&'Table'[end time]<'Table 2'[last_dt]),'Table 2'[last_dt])
VAR _d1='Table'[end time]-_l
VAR _d2=abs(_n-'Table'[end time])
VAR _compare=min(_d1,_d2)
return  if(_compare=_d1,maxx(FILTER('Table 2','Table'[item]='Table 2'[item]&&'Table 2'[last_dt]=_l),'Table 2'[value]),maxx(FILTER('Table 2','Table'[item]='Table 2'[item]&&'Table 2'[last_dt]=_n),'Table 2'[value]))

    pls see the attachment below

1 Reply

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    clteh9 

    pls try this 

    Column = 
VAR _l=maxx(FILTER('Table 2','Table'[item]='Table 2'[item]&&'Table'[end time]>='Table 2'[last_dt]),'Table 2'[last_dt])
VAR _n=minx(FILTER('Table 2','Table'[item]='Table 2'[item]&&'Table'[end time]<'Table 2'[last_dt]),'Table 2'[last_dt])
VAR _d1='Table'[end time]-_l
VAR _d2=abs(_n-'Table'[end time])
VAR _compare=min(_d1,_d2)
return  if(_compare=_d1,maxx(FILTER('Table 2','Table'[item]='Table 2'[item]&&'Table 2'[last_dt]=_l),'Table 2'[value]),maxx(FILTER('Table 2','Table'[item]='Table 2'[item]&&'Table 2'[last_dt]=_n),'Table 2'[value]))

    pls see the attachment below