New Table base on Date Range

DAX
1

Hi Guys,

I have two Fact Tables Fact_Employment_Contracts and Fact_Crew_Activity. Both Tables have a Relationship with Dim_Crew_Profile[CrewProfileSK] and Dim_Date.

On the Fact_Employment_Contracts I have:
CrewProfileSK; CBACode; FromDate; Todate

This bring over time the carea path of the crew.

On the Fact_Crew_Activity I have:
Crew_Profile_SK; Activitycode; ActivitySK; StartDate; Enddate; SiteSK

I need to have a CAlculate table that brings all activities per CBAcode.
Note: one Activity can be efected with more than a CBA, in case the person get promote in the midle an activity:

I that case the same activity need to have two activities split in two lines, so CrewProfile A did Sail from 01/01/2024 to 28/02/2024 but from 01/01/2024 with CBAcode 1stcontract, and from 01/02/2024 with CBAcode 2ndContract.

Thank you for your assistance/help
Scenario - CBA per Activity.pbix (985.7 KB)

2

I just add a data sample. The Idea is to create a new Fact table to host all CBA’s per Activity. knowing that a CBA can have a long period and be applied to multi Activities, and at certan time the crew will be promoted, so one Activity and have two CBA’s
Thank you