Forum Discussion
Create forecast aggregate balance by day, using loan start date, maturity date, notional
Hello
I have a large dataset of trades (loans), which I would like to transform into a table showing the aggregate balance sheet runoff over time, with the date being the column header in the resulting table or matrix. In essence this is a balance sheet forecast using contractual data, going out several years. I would like to calculate and show this by day (columns), and then subsequently aggregate by month, year, etc.
Each trade has start date, maturity date and notional, with example data below.
I would like the starting B/S date to be 31/12/21, and cover a 5 year period to 31/12/26.
I believe the right approach might involve creating a separate date table showing all the days from and until these dates, and additionally some kind of measure that applies a filter using the start/maturity dates...e.g. the total for a given day sums all balances for trades whose start dates are <= that day, and whose maturity dates are > that day.
I have tried a few approaches on this theme so far with no success. Im not sure it would be all that helpful to post any of my failed code as a starting point for discussion, hopefully community members will be able to suggest what they would do from scratch.
I have been chasing my tail on this for a few days, so any help would be hugely appreciated!
If I have ommitted any key information please do let me know and I will happily elaborate.
Thank you in advance!
| Trade ID | Start Date | Maturity Date | Remaining Notional |
| 1 | 02-May-21 | 01-May-26 | 146 |
| 2 | 03-Jan-20 | 01-Jan-25 | 37 |
| 3 | 03-Jul-19 | 01-Jul-24 | 89 |
| 4 | 07-Sep-20 | 06-Sep-25 | 129 |
| 5 | 27-Jun-20 | 26-Jun-25 | 100 |
| 6 | 11-Nov-18 | 10-Nov-23 | 56 |
| 7 | 04-Jul-21 | 03-Jul-26 | 70 |
| 8 | 14-Aug-21 | 13-Aug-26 | 137 |
| 9 | 20-Dec-20 | 19-Dec-25 | 92 |
| 10 | 18-Dec-19 | 16-Dec-24 | 40 |
Hi Anonymous ,
I suggest you to create a calendar table and then create a measure and show your result in a matrix.
Calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2025, 12, 31 )), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "End Date of Year", DATE ( YEAR ( [Date] ), 12, 31 ) )Measure:
Basic = VAR _ENDDATE = SELECTEDVALUE ( 'Calendar'[End Date of Year] ) RETURN IF ( _ENDDATE >= MAX ( 'Table'[Start Date] ) && _ENDDATE <= MAX ( 'Table'[Maturity Date] ), CALCULATE ( SUM ( 'Table'[Remaining Notional] ) ) ) + 0Forecast Remaining Notional = SUMX ( FILTER ( 'Table', SELECTEDVALUE ( 'Calendar'[End Date of Year] ) <> BLANK () ), [Basic] )Create a matrix, you need to turn off "Stepped layout" in Row headers and turn off the subtotal you don't need in Format. Result is as below.
You can download my sample file to learn more details.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
- lbendlin
Super User
You may need to explain what a notional is. It would also help if you could indicate the expected outcome based o the sample data you provided.
Sorry, notional just means balance.
OK, so if I calculate this for the year end dates as a concise example, we would get something like the below table. The forumulae in cell E2, for example, is very simply =IF(AND(B2<=E1,C2>E1),D2,0).
As I say, the actual output would need to be for all days through time, not just the year end dates.
Trade ID
Start Date Maturity Date Remaining Notional 31/12/21 31/12/22 31/12/23 31/12/24 31/12/25 1 02-May-21 01-May-26 146 146 146 146 146 146 2 03-Jan-20 01-Jan-25 37 37 37 37 37 0 3 03-Jul-19 01-Jul-24 89 89 89 89 0 0 4 07-Sep-20 06-Sep-25 129 129 129 129 129 0 5 27-Jun-20 26-Jun-25 100 100 100 100 100 0 6 11-Nov-18 10-Nov-23 56 56 56 0 0 0 7 04-Jul-21 03-Jul-26 70 70 70 70 70 70 8 14-Aug-21 13-Aug-26 137 137 137 137 137 137 9 20-Dec-20 19-Dec-25 92 92 92 92 92 0 10 18-Dec-19 16-Dec-24 40 40 40 40 0 0 Total 896 896 840 711 353 - lbendlin
see attached
Hi Anonymous ,
I suggest you to create a calendar table and then create a measure and show your result in a matrix.
Calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2025, 12, 31 )), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "End Date of Year", DATE ( YEAR ( [Date] ), 12, 31 ) )Measure:
Basic = VAR _ENDDATE = SELECTEDVALUE ( 'Calendar'[End Date of Year] ) RETURN IF ( _ENDDATE >= MAX ( 'Table'[Start Date] ) && _ENDDATE <= MAX ( 'Table'[Maturity Date] ), CALCULATE ( SUM ( 'Table'[Remaining Notional] ) ) ) + 0Forecast Remaining Notional = SUMX ( FILTER ( 'Table', SELECTEDVALUE ( 'Calendar'[End Date of Year] ) <> BLANK () ), [Basic] )Create a matrix, you need to turn off "Stepped layout" in Row headers and turn off the subtotal you don't need in Format. Result is as below.
You can download my sample file to learn more details.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Rico
Many thanks for taking the time to look at this. I have modified the solution to provide a monthly view, and it seems to work fine!
I have one further questions if I may. I have created a table report with two columns: calendar months and the Forecast Remaining Notional measure. Is it possible to achieve the same simple table as an underlying data table, as opposed to a report, i.e. run the measure within a new column for the list of "End Date of Months" per the calendar? Reason being is that this would then allow me to export the table to excel.
Thanks
