Hey guys, I need your help!
The data basis consists of two tables. One table is called 'Calculation Types' and the other table is called 'Plan Entries'.
In the 'Calculation Types' table, there can be varying numbers of rows per loan number. The different rows provide information about the conditions within a time period. Therefore, the table 'Calculation Types' consists of 4 columns:
1. Loan number
2. Date from
3. Date to
4. Simulation
example:
| Loan number | | Date from | | Date to | | Simulation |
| XY001 | | 01.01.2015 | | 31.12.2025 | | 0 |
| XY001 | | 01.01.2026 | | 31.12.2036 | | 1 |
| XY002 | | 01.01.2018 | | 31.12.2028 | | 0 |
| XY003 | | 01.01.2020 | | 30.06.2024 | | 0 |
| XY003 | | 01.07.2024 | | 30.06.2034 | | 1 |
The two date columns indicate the duration for which an interest rate is valid for a loan. If a new interest rate applies, a new row is created with new 'Date from' and 'Date to' values. The column that is crucial for my requirement is 'Simulation'. It contains either a value of 1 or 0 and indicates whether the corresponding row represents a fixed agreed condition or if the row is for simulation purposes only. '1' stands for simulation, '0' stands for no simulation. Not every loan has a row with a simulation.
In the 'Plan Entries' table, all transactions related to the rows in the 'Calculation Types' table can be found. This means that there are multiple rows per loan. The columns in the 'Plan Entries' table are named
1. Loan number
2. Due date
3. Amount
example:
| Loan Number | | Due date | | Amount |
| XY001 | | 01.12.2025 | | 500,00 € |
| XY001 | | 01.01.2026 | | 350,00 € |
| XY001 | | 01.02.2026 | | 350,00 € |
| ... | | | | |
| XY002 | | 15.01.2018 | | 400,00 € |
| XY002 | | 15.02.2018 | | 400,00 € |
| ... | | | | |
| XY003 | | 01.06.2024 | | 700,00 € |
| XY003 | | 01.07.2024 | | 700,00 € |
| XY003 | | 01.08.2024 | | 700,00 € |
| ... | | | | |
The two tables are connected through the respective column 'Loan number'. The 'Due date' always falls between the 'Date from' and 'Date to' values in the 'Calculation Types' table.
I need two measures: Both measures should sum the 'Amount' column in the 'Plan Entries' table, but the first measure should only calculate amounts before the start of a simulation for each loan, and the second measure should only calculate amounts within the simulation period for each loan.
Is there any solution?
