Data Model with SCD type 4 implementation

Hello santhidhanuskod,

Can you please try this approach:

1. Create a Date Dimension Table

DateTable = ADDCOLUMNS (
    CALENDAR (DATE(2000,1,1), TODAY()),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Year-Month", FORMAT([Date], "YYYY-MM")
)

2. Create a Bridge Table

BridgeTable = DISTINCT(
    UNION(
        SELECTCOLUMNS( LiveTable, "Header", LiveTable[Header], "RecordDate", LiveTable[RecordDate]),
        SELECTCOLUMNS( HistoryTable, "Header", HistoryTable[Header], "RecordDate", HistoryTable[RecordDate])
    )
)

Hi santhidhanuskod 

Usually, creating a bridgiing table is a general solution for many-to-many relationships.

Many-to-many relationship guidance - Power BI | Microsoft Learn

Connecting Fact Tables in Microsoft Fabric: A Brid... - Microsoft Fabric Community

Or you can use the Merge queries to merge tables with the same key value in the Power query.

Merge queries overview - Power Query | Microsoft Learn

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.