cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## To create list of dates to compare with other dates column

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.

1 ACCEPTED SOLUTION
Community Support

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)``````

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

)``````

Here is my preview:

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.

3 REPLIES 3
Community Support

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]
)``````

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.

Helper III

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.

Community Support

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)``````

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

)``````

Here is my preview:

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors