PowerBI - Choosing a relative field between 2 dates

[tchooper7]

I need to lookup a value and combine it in a another table in Power BI

Table 1:

Effective Start Date eg 1 July 2021

Effective End Date (this could be null as the end date may not have occurred yet 30 June 2022

Customer Number 15537

Table 2

Date

Name

Example is :

Date is the 15th July 2021

Name is Teresa

Customer number 15537

I want to return Teresa into a column in Table 1

Have tried a few date and time functions with no luck

Does anyone have any thoughts

Thank you

[tchooper7]

Thank you for your assistance - in the Transform Data area the table is correct and the table has 7.4M rows - when apply the changes the table is created 11M rows - so am trying to work out what is happening there - would you have any thoughts please

[v-kkf-msft]

Hi @tchooper7 ,

Has your problem been solved? If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @tchooper7 ,

This is strange. Because I added {0} in the formula, it will return for each row the first name that satisfies the condition. So it should not increase the number of rows. Does the result of those added duplicate rows make any difference?

Best Regards,
Winniz

[v-kkf-msft]

Hi @tchooper7 ,

Please try using DAX to create the new column.

Name = 
CALCULATE (
    MAX ( 'Table 2'[Name] ),
    FILTER (
        'Table 2',
        'Table 2'[Date] >= EARLIER ( 'Table 1'[Start Date] )
            && OR (
                'Table 2'[Date] <= EARLIER ( 'Table 1'[End Date] ),
                'Table 1'[End Date] = BLANK ()
            )
    )
)

Or create custom column in Power Query.

= let 
  s = [Start Date], e = [End Date]
in
  Table.SelectRows(#"Table 2", each [Date] >= s and ([Date] <= e or e = null)){0}[Name]

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tchooper7]

Ps  Thank you for trying to help me

[v-kkf-msft]

Hi @tchooper7 ,

Does this result match your desired output?

= let CurDate = [Date of Transaction]
in Table.SelectRows( Table.Sort(#"Table 1",{{"Start Date", Order.Descending}}), each [#"Start Date"] <= CurDate and ([#"End Date"] >= CurDate or [#"End Date"] = null)){0}[Name]

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tchooper7]

I don't think I explained it correctly - apologies.

In Table 1 has a start and end date and a field I want to appear in Table 2 according to the date in Table 2

Example

Table 1                 Start Date         End Date       Name   

Line 1 :                 1/7/2020          30/6/2021      Teresa

Line 2 :                  1/7/2019                                Joe

I want to appear in Table 2

                                           Date of Transaction         New Column

Line 1                                       15/7/2020                  Teresa

Line  2                                           10/7/2019              Joe

Any thoughts please

Teresa