Calculate sum using different relationships based on condition

Hello everyone,

I'm trying to build a rather complicated DAX measure but I have no luck getting the expected the result. Here's a simplified description of my data:

I have a table that contains bookings and the amount of the booking. There is a company_id column which links to a company. Each booking has a booking_period_id which links to a booking period table which contains this ID and the corresponding date. booking_period_ids are determined per company so two companies can't have the same booking_period_id but may have the same booking period date. This is not a classical date table though because the dates may not be continuous. Each booking also has an account_schema_id1 and an account_schema_id2 which link to a account schema table. The account_schema_id1 has a matching account_schema_id2 value. Additionally, there is a flag called is_switched. is_switched indicates which account_schema_id should be used for the relationship (i. e. where the sum should be displayed). is_switched is determined on a monthly basis for the company and account_schema_id. The measure I'm trying to build should do the following:

• Identify the last (i. e. most recent) value of the is_switched column for the company and each account_schema_id1 based on the booking period selected; this need to be done on row-level
• If the last/most recent value of is_switched for the company and account_schema_id1 is 1 then display the sum using the relationship that links account_schema_id2 to the account schema table (this is the inactive relationship); the sum should only sum up column values of amount for which the overall most recent value (where row booking period <= selected booking period) of is_switched is 1
• If the last/most recent value of is_switched for the company and account_schema_id1 is 0 then display the sum using the relationship that links account_schema_id1 to the account schema table (this is the active relationship); the sum should only sum up column values of amount for which the overall most recent value (where row booking period <= selected booking period) of is_switched is 0
  
  

In other words, if the most recent value (based on the selected booking period) of the is_switched column for a company and account_schema_id is 1 then I want to filter the bookings table to these rows, sum them up and display them for account_schema_id2. 

If the most recent value (based on the selected booking period) of the is_switched column for a company and account_schema_id is 0 then I want to filter the bookings table to these rows, sum them up and display them for account_schema_id1. 

Here's an example and the expected result:
Booking table:

Booking period table:

Account schema table:

When a user selects the booking period 01/02/2023 I expect the following behavior for company 1:

• For company 1 the last is_switched value for account_schema_id1 where the id is 1 is  1 
• For company 1 the last is_switched value for account_schema_id1 where the id is 2 is  0 
• Sum all amounts where last is_switched value is 1 and use the relationship account_schema_id2 to account_schema_id (booking_ids 1, 2, 3)
• Sum all amounts where last is_switched value is 0 and use the relationship account_schema_id1 to account_schema_id (booking_ids 4, 5)

The result for company 1 should be:

Only using the top_name field, the result should look like this:

When a user selects the booking period 01/02/2023 I expect the following behavior for company 2:

• For company 2 the last is_switched value for account_schema_id1 where the id is 5 is  1

• For company 2 the last is_switched value for account_schema_id1 where the id is 6 is  1

• Sum all amounts where last is_switched value is 1 and use the relationship account_schema_id2 to account_schema_id

• Sum all amounts where last is_switched value is 0 and use the relationship account_schema_id1 to account_schema_id (booking_ids 6, 7, 8, 9, 10)

The result for company 2 should be:

Only using the top_name field, the result should look like this: