Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create relationship between two table with start and end date

I have two table first table is about ID and Start and End Time that Value Change based on Time

second table is ID and Date. I want to get the value from first table in second table by mapping ID and date (if date is in between start and end date use that row for example ID A0001 Date 02-13-2023 is match with first row from first table then the value is 2000)

 

First Table

IDStart DateEnd DateValue
A0001 02-10-2023 02-14-2023 2000
A0001 01-01-2023 02-09-2023 1000
A0001 11-01-2022 12-31-2022 3000
B0001 02-01-2023 02-14-2023 1500
B0001 12-01-2022 01-31-2023 500

 

Second Table

IDDateValue (Desire Cloumn)
A0001 02-13-2023 2000
A0001 01-20-2023 1000
B0001 02-10-2023 1500
B0001 12-25-2022 500
B0001 12-13-2022 500

 

Are there any solution to map relationship like this?

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    One of ways to achieve this is to create a datamodel like below.

    Please check the below picture and the attached sample pbix file.

    It is for creating a new column in the Value Target table.

     

     

     

     

     

    Value CC =
VAR _id =
    RELATED ( 'ID'[ID] )
RETURN
    MAXX (
        FILTER (
            Data,
            Data[ID] = _id
                && Data[Start Date] <= 'Value Target'[Date]
                && Data[End Date] >= 'Value Target'[Date]
        ),
        Data[Value]
    )

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    One of ways to achieve this is to create a datamodel like below.

    Please check the below picture and the attached sample pbix file.

    It is for creating a new column in the Value Target table.

     

     

     

     

     

    Value CC =
VAR _id =
    RELATED ( 'ID'[ID] )
RETURN
    MAXX (
        FILTER (
            Data,
            Data[ID] = _id
                && Data[Start Date] <= 'Value Target'[Date]
                && Data[End Date] >= 'Value Target'[Date]
        ),
        Data[Value]
    )