Hello everyone,
I'm new to Power BI from heavy Excel use and have been hacking away learning some of the basic functionality. I've run into a complicated situation that has me stumped on how to tackle.
I have a list of loans that are related via a "parent" (which basically means the first loan) and a number of child loans, which could be any number. My data contains the balances of each loan when the balance changes (system generated and there are too many underlying transactions to do this with transaction-level data). The balance changes may occur on any date and while they tend to occur on the same day, sometimes they do not. I need a column which sums the most recent balance record for the given date. I've mocked it up in the data below as ParentLevelPrincipalBalance. The logic would be:
For each row, sum the most recent PrincipalBalance record for each loan within the parent group ("Parent if parent" column) that is dated equal to or less than the row's BalanceDate.
As an example, the sum of the balances are $0 on 12/31/20 , then on 1/1/2020 the new balance record for loan 0001A would be used in the sum process. This wouldn't be too difficult to pull off in a VBA loop but I'm not sure how to do it in DAX. Is there a way to use filtering to isolate the 3 loans' most recent records relative to the balance date in the 0001A parent group and sum those?
The second, related situation is that the loans are related through LoanGroup, which is basically the same concept but can combine multiple parent groups in the LoanGroupPrincipalBalance.
I hope that I've described this clearly enough to be helpful. Thank you very much!
I'm new to Power BI from heavy Excel use and have been hacking away learning some of the basic functionality. I've run into a complicated situation that has me stumped on how to tackle.
I have a list of loans that are related via a "parent" (which basically means the first loan) and a number of child loans, which could be any number. My data contains the balances of each loan when the balance changes (system generated and there are too many underlying transactions to do this with transaction-level data). The balance changes may occur on any date and while they tend to occur on the same day, sometimes they do not. I need a column which sums the most recent balance record for the given date. I've mocked it up in the data below as ParentLevelPrincipalBalance. The logic would be:
For each row, sum the most recent PrincipalBalance record for each loan within the parent group ("Parent if parent" column) that is dated equal to or less than the row's BalanceDate.
As an example, the sum of the balances are $0 on 12/31/20 , then on 1/1/2020 the new balance record for loan 0001A would be used in the sum process. This wouldn't be too difficult to pull off in a VBA loop but I'm not sure how to do it in DAX. Is there a way to use filtering to isolate the 3 loans' most recent records relative to the balance date in the 0001A parent group and sum those?
The second, related situation is that the loans are related through LoanGroup, which is basically the same concept but can combine multiple parent groups in the LoanGroupPrincipalBalance.
I hope that I've described this clearly enough to be helpful. Thank you very much!
| LoanNumber | ParentLoan | Parent if Parent | BalanceDate | PrincipalBalance | LoanGroup | ParentLevelPrincipalBalance | LoanGroupPrincipalBalance |
| 0001A | 0001A | 12/31/2020 | $ - | CLIENT ONE | $ - | $ - | |
| 0001B | 0001A | 0001A | 12/31/2020 | $ - | CLIENT ONE | $ - | $ - |
| 0001C | 0001A | 0001A | 12/31/2020 | $ - | CLIENT ONE | $ - | $ - |
| 0002A | 0002A | 1/13/2020 | $ 50,000.00 | CLIENT ONE | $ 50,000.00 | $ 50,000.00 | |
| 0001A | 0001A | 1/1/2021 | $ 100,000.00 | CLIENT ONE | $ 100,000.00 | $ 150,000.00 | |
| 0001B | 0001A | 0001A | 3/31/2021 | $ 25,000.00 | CLIENT ONE | $ 125,000.00 | $ 175,000.00 |
| 0001C | 0001A | 0001A | 4/30/2021 | $ 10,000.00 | CLIENT ONE | $ 135,000.00 | $ 185,000.00 |
| 0002A | 0002A | 5/31/2021 | $ 49,000.00 | CLIENT ONE | $ 49,000.00 | $ 184,000.00 | |
| 0001A | 0001A | 6/30/2021 | $ 99,000.00 | CLIENT ONE | $ 146,000.00 | $ 195,000.00 | |
| 0001B | 0001A | 0001A | 6/30/2021 | $ 24,000.00 | CLIENT ONE | $ 146,000.00 | $ 195,000.00 |
| 0001C | 0001A | 0001A | 6/30/2021 | $ 23,000.00 | CLIENT ONE | $ 146,000.00 | $ 195,000.00 |
| 0003A | 0003A | 7/1/2021 | $ 400,000.00 | CLIENT TWO | $ 400,000.00 | $ 400,000.00 |
