To create list of dates to compare with other dates column

[Jessica_17]

I have a column A in table A with some dates and Column B in table B with other dates

Table A

09-09-2023 09:10

09-09-2023 10:23

09-09-2023 10:52

09-09-2023 22:25:19

09-09-2023 23:27:20

09-09-2023 23:45:24

Table B

09-09-2023 07:41

09-09-2023 07:42

09-09-2023 07:45

09-09-2023 09:18

09-09-2023 10:21

09-09-2023 10:23

09-09-2023 10:42

Output

table A	Table B	Output
09-09-2023 09:10	09-09-2023 07:41	2023-09-09 07:41:23.000 +0530
09-09-2023 09:10	09-09-2023 07:42	2023-09-09 07:42:55.000 +0530
09-09-2023 09:10	09-09-2023 07:45	2023-09-09 07:45:31.000 +0530
09-09-2023 09:10	09-09-2023 09:18	2023-09-09 09:10:14.000 +0530
09-09-2023 10:23	09-09-2023 10:21	2023-09-09 10:21:54.000 +0530
09-09-2023 10:23	09-09-2023 10:23	2023-09-09 10:23:00.000 +0530
09-09-2023 10:52	09-09-2023 10:42	2023-09-09 10:42:50.000 +0530

According to the output I want, I would need to convert the table A column A dates into List and have to compare Column B dates with list such as if the lets take this date from column b "09-09-2023 07:41" it should check whether this values has any same or higher value then this in column A, if it has higher, then will use that date as next date to compare column B next set of dates and if it lower will use it to compare next set of dates of column B and if same at both side  then will switch to next date of column B and will compare next set of dates of column A as shown in output table, and which ever seems lowest  will be stored in 3rd column afterwards.

EDIT:- Both of these has one more column whose value are same for all value that "ABC", all rows has this value in next column and it changes according to what value we are passing and its respective dates.

[Anonymous]

Hi, @Jessica_17 

Maybe you can create columns, here is DAX:

NextDate = 
VAR CurrentDate = 'Table B'[B]
RETURN
MINX(
    FILTER('Table A', 'Table A'[A]>= CurrentDate),
    'Table A'[A]
)

Output = 
IF('Table B'[B]<='Table B'[NextDate],
'Table B'[B],
'Table B'[NextDate]
)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[Jessica_17]

HI @Anonymous 

In thi where 9:18 am timestamp is in left columns it should have value 9:10:00 am in right column then in next row iteration it should change.

[Anonymous · Accepted solution]

Hi, @Jessica_17 

You can use Crossjoin to create a new table:

Table = CROSSJOIN(SELECTCOLUMNS('Table B',"B",'Table B'[B]),SELECTCOLUMNS('Table A',"A",'Table A'[A]))

Create a new column to calculate the difference time to find the closest time period, Then create a new column to find out when it matches

DiffMin = ABS(DATEDIFF('Table'[A],'Table'[B],MINUTE))

Column = 
IF(CALCULATE(MIN('Table'[DiffMin]),FILTER(ALL('Table'),'Table'[B]=EARLIER('Table'[B])))='Table'[DiffMin],1,0)

Create a measure to match the time:

NextDate = 
CALCULATE(MAX('Table'[A]),'Table'[Column]=1)

About Output:

Output = 
IF('Table'[Column] = 1,
    IF('Table'[B]<='Table'[A],
    'Table'[B],
    'Table'[A]),
    BLANK()

)

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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