new column based on if date is between two dates then fill with text

[Anonymous]

Hi all, 

first post here. I dont know if it is possible with PBI but i have two tables:

Table 1:

[SERVICE_DATE] [New Column]

Table 2:

[DATE1] [DATE2] [TEXT MESSAGE]

I am looking to fill the [NEW COLUMN] in Table 1 with the [TEXT MESSAGE] from table 2 if [SERVICE_DATE] is equal to or between [DATE1] and [DATE2]. 

im lost and have tried a couple of different options with zero sucess. 

[Anonymous · Accepted solution]

HI @Anonymous,

You can use the following calculated column formula in table1 if it suitable for your requirement:

formual =
CONCATENATEX (
    FILTER (
        Table2,
        Table2[DATE1] <= EARLIER ( Table1[SERVICE_DATE] )
            && Table2[DATE2] >= EARLIER ( Table1[SERVICE_DATE] )
    ),
    [TEXT MESSAGE],
    ","
)

Regards,

Xiaoxin Sheng