How to return a value associated with the nearest time stamp in another table?

PomJack — Wed, 21 Aug 2024 15:49:36 GMT

I have two tables, one that has a list of events and time stamps, time stamps that a fixture was used and the number of times that fixture has been used. I would like to pull the fixture and number of uses that most closely matches the timestamp of the event. See the example tables for a better idea of what is in each table and what I would like the end result to be.

Event Table
Time Stamp	Event Name
8/1/2024 10:00	A
8/1/2024 10:23	A
8/1/2024 10:44	A
8/1/2024 11:03	B

Data Table
Time Stamp	Fixture	Fixture Hits
8/1/2024 9:40	X	10
8/1/2024 9:45	X	11
8/1/2024 9:54	X	12
8/1/2024 10:02	Y	18
8/1/2024 10:06	X	13
8/1/2024 10:21	Y	19
8/1/2024 10:30	Y	20
8/1/2024 10:40	Z	5
8/1/2024 10:54	X	14
8/1/2024 11:05	X	15
8/1/2024 11:07	Y	21

Output Table
Time Stamp	Event Name	Fixture	Fixture Hits
8/1/2024 10:00	A	Y	18
8/1/2024 10:23	A	Y	19
8/1/2024 10:44	A	Z	5
8/1/2024 11:03	B	X	15

I've tried the expression below based on the solution from a similar question, but I am getting this error: "A table of multiple values was supplied where a single value was expected"

column =

VAR _Current = 'Event Table'[TimeStamp]

VAR _Cross = CROSSJOIN('Event Table','Data Table')

VAR _Tbl = ADDCOLUMNS(_Cross, "@difference",ABS(INT('Data Table'[TimeStamp]-'Event Table'[Time Stamp])))

VAR _MinDiff = MINX(_Tbl,[@difference])

return SELECTCOLUMNS(FILTER(_Tbl,[@difference] = _MinDiff),"newCol", 'Data Table'[TimeStamp])

Any help would be appreciated.

Re: How to return a value associated with the nearest time stamp in another table?

Anonymous — Thu, 22 Aug 2024 06:12:50 GMT

Hi, PomJack

You can try the following methods.

Column:

Nearest time = Var _Min=CALCULATE(MIN('Data Table'[Time Stamp]),FILTER('Data Table',[Time Stamp]>EARLIER('Event Table'[Time Stamp]))) Var _Max=CALCULATE(MAX('Data Table'[Time Stamp]),FILTER('Data Table',[Time Stamp]<EARLIER('Event Table'[Time Stamp]))) Var _Mindiff=ABS(DATEDIFF([Time Stamp],_Min,MINUTE)) Var _Maxdiff=ABS(DATEDIFF([Time Stamp],_Max,MINUTE)) RETURN IF(_Maxdiff<_Mindiff,_Max,_Min)

Fixture = CALCULATE(MAX('Data Table'[Fixture]),FILTER('Data Table',[Time Stamp]=EARLIER('Event Table'[Nearest time])))

Fixture Hits = CALCULATE(MAX('Data Table'[Fixture Hits]),FILTER('Data Table',[Time Stamp]=EARLIER('Event Table'[Nearest time])))

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

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

topic How to return a value associated with the nearest time stamp in another table? in DAX Commands and Tips

How to return a value associated with the nearest time stamp in another table?

Re: How to return a value associated with the nearest time stamp in another table?