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_id | amount | company_id | booking_period_id | account_schema_id1 | account_schema_id2 | is_switched |
| 1 | 10 | 1 | 2 | 1 | 3 | 1 |
| 2 | 5 | 1 | 4 | 1 | 3 | 0 |
| 3 | 10 | 1 | 1 | 1 | 3 | 1 |
| 4 | 5 | 1 | 1 | 2 | 4 | 1 |
| 5 | 10 | 1 | 2 | 2 | 4 | 0 |
| 6 | 5 | 2 | 7 | 5 | 8 | 0 |
| 7 | 10 | 2 | 5 | 5 | 8 | 1 |
| 8 | 5 | 2 | 7 | 5 | 8 | 0 |
| 9 | 10 | 2 | 5 | 6 | 7 | 1 |
| 10 | 5 | 2 | 5 | 6 | 7 | 1 |
Booking period table:
| booking_period_id | booking_period |
| 1 | 1/1/2023 |
| 2 | 1/2/2023 |
| 3 | 1/3/2023 |
| 4 | 1/4/2023 |
| 5 | 1/1/2023 |
| 6 | 1/2/2023 |
| 7 | 1/3/2023 |
Account schema table:
| account_schema_id | name | top_name |
| 1 | Test1 | TestA |
| 2 | Test2 | TestB |
| 3 | Test3 | TestA |
| 4 | Test4 | TestA |
| 5 | Test5 | TestA |
| 6 | Test6 | TestB |
| 7 | Test7 | TestA |
| 8 | Test8 | TestA |
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:
| top_name | name | sum of amount (measure) |
| TestA | Test1 | 0 |
| TestB | Test2 | 15 |
| TestA | Test3 | 25 |
| TestA | Test4 | 0 |
| TestA | Test5 | 0 |
| TestB | Test6 | 0 |
| TestA | Test7 | 0 |
| TestA | Test8 | 0 |
Only using the top_name field, the result should look like this:
| top_name | sum of amount (measure) |
| TestA | 25 |
| TestB | 15 |
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:
| top_name | name | sum of amount (measure) |
| TestA | Test1 | |
| TestB | Test2 | |
| TestA | Test3 | |
| TestA | Test4 | |
| TestA | Test5 | |
| TestB | Test6 | |
| TestA | Test7 | 15 |
| TestA | Test8 | 20 |
Only using the top_name field, the result should look like this:
| top_name | sum of amount (measure) |
| TestA | 35 |
| TestB | 0 |
