Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Jessica_17
Helper III
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 ATable B Output
09-09-2023 09:1009-09-2023 07:412023-09-09 07:41:23.000 +0530
09-09-2023 09:1009-09-2023 07:422023-09-09 07:42:55.000 +0530
09-09-2023 09:1009-09-2023 07:452023-09-09 07:45:31.000 +0530
09-09-2023 09:1009-09-2023 09:182023-09-09 09:10:14.000 +0530
09-09-2023 10:2309-09-2023 10:212023-09-09 10:21:54.000 +0530
09-09-2023 10:2309-09-2023 10:232023-09-09 10:23:00.000 +0530
09-09-2023 10:5209-09-2023 10:422023-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

Hi, @Jessica_17 

You can use Crossjoin to create a new table:

vyohuamsft_0-1709887002634.png

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)

 

vyohuamsft_1-1709887660350.png

 

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:

vyohuamsft_2-1709888935825.png

 

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.

View solution in original post

3 REPLIES 3
v-yohua-msft
Community Support
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]
)

 

vyohuamsft_0-1709790908467.png

 

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.

HI @v-yohua-msft 

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.

Hi, @Jessica_17 

You can use Crossjoin to create a new table:

vyohuamsft_0-1709887002634.png

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)

 

vyohuamsft_1-1709887660350.png

 

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:

vyohuamsft_2-1709888935825.png

 

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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