Many-to_many relationship with dates

[AAA_PBI]

Hi everyone,

I am struggling with a problem I can't figure out how to solve. I have two tables, the first one containing the report of the activities executed in a specific date within a certain Service (see screenshot below):

The other table contains the description of each Service ID related to a specified date-range:

The two tables have a many-to-many relationship through the "Service ID" col. What I want to do is to insert a new calculated column in the first table in which I want to get the description associated to the correct date-range.

i.e. in line #1 I want to get the description of the Service executed on the 15th October 2020, that is "Service A (2020)", while for the line #4 I want to see the description "Service A (2021)", since the activity has been carried out in 2021.

Is there any way to achieve this? RELATED doesn't work with many-to-many relationships, I have tried to use LOOKUPVALUE but the issue comes when trying to compare a single date with a date-range.

Any help would be appreciated.

Thanks in advance.

[amitchandak]

@AAA_PBI , Add new columns in Table 1

Minx(filter(Table2, Table2[Start Date] <= Table1[Date] && Table2[End Date] >= Table1[Date]),Table1[Start Date])

maxx(filter(Table2, Table2[Start Date] <= Table1[Date] && Table2[End Date] >= Table1[Date]),Table1[End Date])

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[AAA_PBI · Accepted solution]

Hi @amitchandak, 

thanks for your reply.

Actually, trying to implement the solution you proposed, I figured out that the following formula was doing the whole process in only one step:

Col = MAXX(FILTER(Table_2,Table_2[Start_date]<=Table_1[Date] && Table_2[End_date]>=Table_1[Date] && Table_2[Service ID]=Table_1[Service ID]),Table_2[Descr])

Thanks again!