Joining table on Date with another table and two Dates?

[coleent]

Hi, is it possible to create a relationship between two tables with one date column in the first table and two date columns in the second table? Or would it be better to just write it into the query when loading the data?

Example:

Table: TimeDetail
Column: Date

Table: PayPeriodSchedule
Column: PayPeriodStartDate
Column: PayPeriodEndDate

Would like to join TimeDetail table to PayPeriodSchedule table if Date is >= PayPeriodStartDate and <= PayPeriodEndDate.

I originally wrote the join in my query (resulting in a single table) but I want to know if it can be kept as separate queries/tables in Power BI and the relationship created there.

Thank you.

[DAX0110 · Accepted solution]

You would need a row identifier column in PayPeriodSchedule : let's call it PayPeriodID

If I assume correctly that you want to identify the pay period  that a TimeDetail entry belongs to, you can add a calculated column to the TimeDetail table with this formula:

=VAR thisDate = TimeDetail[Date]
RETURN CALCULATE(
    FIRSTNONBLANK(PayPeriodSchedule[PayPeriodID],0)
    , FILTER( ALL(PayPeriodSchedule)
        , PayPeriodSchedule[PayPeriodStartDate] <= thisDate

       && thisDate <= PayPeriodSchedule[PayPeriodEndDate]
        )
    )