How to join a dimension using effective from and to dates

[ADosani]

Hello Everyone, 

How can I join two dimension tables (Claim Current and Claim History) in Power BI using a common key. Then we need join to the fact table such that the Transaction date is between the effective from and to dates from the History Table.

eg. current.commonkey = history.commonkey and fact.transactiondate between history.effectiveFrom and history.effectiveTo

Is it possible to do this in PowerBI, instead of Source

[v-karpurapud]

Hi @ADosani 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @ADosani 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @ADosani 

Could you please confirm if your query have been resolved the solution provided by @johnt75 ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[johnt75 · Accepted solution]

Make sure that the history table has a unique identifier for each row. If it doesn't already, use Power Query to add an index column.

Link the current table to the history table in a one-to-many relationship.

Create a calculated column in the fact table to get the appropriate index from the history table

History Index =
SELECTCOLUMNS (
    FILTER (
        'History',
        'History'[Common Key] = 'Fact'[Common Key]
            && 'History'[Effective From] <= 'Fact'[Transaction Date]
            && (
                ISBLANK ( 'History'[Effective To] )
                    || 'History'[Effective To] >= 'Fact'[Transaction Date]
            )
    ),
    "@value", 'History'[Index]
)

Then link History[Index] to Fact[History Index] in a one-to-many relationship.