Getting info from 2 Tables based on Period Selection

Hello,

Below a Diagram of the DataSet in the attached Power BI Desktop sample:

Contract is a shared dimension between ContractStatuses and ContractTransactions fact tables.

The Contract has ContractAltKey as a business key with many images marked by different Seq#. ContractKey is the primary key.

If we select Period 31/12/2017, then for ContractAltKey= 719496, the latest record in ContractStatuses table below as per LastUpdateDate will be the one with ContractStatusesKey = 3958349, as marked below in orange.

If we select Period 31/12/2015, then for ContractAltKey= 719496, the latest record in ContractStatuses as per LastUpdateDate will be the one with ContractStatusesKey = 3051303, as marked below in orange.

Now after getting this record, I will need to check that the ContractStatusCode of this latest row, is not “C”.

Then join with ContractTransactions table based on ContractKey and Seq# fields (The cells marked in Black) of the most recent record based on Period filter. Example of record expected from ContractTransactions for Period 31/12/2015

Then Retrieve corresponding Amount 1 or do other calculations on those records in Contract Transactions.

N.B. This can become more complex in the sense that Contract Transactions fact table might contain multiple records matching the ContractKey and Seq#, and we need to get the Maximum(TransactionKey) that satisfies the join condition. As a first problem, I'm looking into possibility of retrieving Amount 1 based on above logic.

Link to the attached the Sample Power BI  Sample Power BI Desktop

Many thanks for any suggestion on the approach to take.

Grace