Using a calculated value to populate another expression

[Brip51]

Hi.  This is a little strange, but I have a request to build something this way-

I have an expression that calculates a value (closing balance).  The report is kind of like a ‘P&L’ template as the values in the columns are added to some of the preceding values.

The Closing Balance is

Net opening insurance contract balances + Total Changes In The Statement of Profit or Loss & OCI + Total Cash Flows

At the end of a calendar year the closing balance (17,962,052) needs to be used as the next year’s opening balance.  So if you select booking date ‘2024-01-31’ the opening balance is 17,962,052.

I am doing that by using set analysis to calculate the 2024 opening balance as the closing balance (Net opening insurance contract balances + Total Changes In The Statement of Profit or Loss & OCI + Total Cash Flows) but with the 2023 YTD date ranges.

That works for 2023 to 2024 booking year.  My issue is it does not work for any of the next years.  Since I am using the opening balance, which is a calculation –

(

(Num(Sum({$<[Cash Booking Date] = , [Booking Date] = ,business_effective_year_month = {"<=$(=Max([To Book Year]-1)&12)"}, [UW Year] ={'>=2021<$(=max([To Book Year]))'}>}[Earned Premium Est Inc])/ (Divider),'#,##0.')

-

Num(Sum({$<[Booking Date] =, [Cash Booking Date] = ,[Cash Booking Date Year] = {'>=2021<$(=max([To Book Year]))'}>}cash_premium)/ (Divider),'#,##0.'))

-

(Num(Sum({$<[Cash Booking Date] = , [Booking Date] = ,business_effective_year_month = {"<=$(=Max([To Book Year]-1)&12)"}, [UW Year] ={'>=2021<$(=max([To Book Year]))'}>}[Acquisition Expense Est Inc])/ (Divider),'#,##0.')

-

Num(Sum({$<[Booking Date] =, [Cash Booking Date] = ,[Cash Booking Date Year] ={'>=2021<$(=max([To Book Year]))'}>}cash_acquisition_cost)/ (Divider),'#,##0.'))

)*-1,

To calculate the prior year closing balance it gives me 19,287,125.  That is because the actual calculated opening balance for 2024 is 17,213,966 ,not the 17,962,052 that I am ‘rolling over’ from 2023 closing balance to the 2024 opening balance. I want the 2024 closing balance (20,035,210) to populate the 2025 opening balance.

What I am struggling with is how I can always take the prior year closing balance and use that as the opening balance (as I am using the calculated prior year opening balance, which will always be incorrect- why I need to use the closing balance number instead- in the closing balance calculation).  Is there a way to save that number (maybe into a variable)???

Or a different approach that I am missing?

Any help on this would be greatly appreciated.

Thank you,

Brian

[TauseefKhan]

Hi @Brip51,

You Need to First, calculate the closing balances for each year.
Create a variable for:

SET NetOpeningBalance = Sum([Net Opening Balance]);
SET TotalPLOCIChanges = Sum([Total Changes In The Statement of Profit or Loss & OCI]);
SET TotalCashFlows = Sum([Total Cash Flows]);

2- Create Closing Balance for each Year
SET ClosingBalance2023 = $(NetOpeningBalance) + $(TotalPLOCIChanges) + $(TotalCashFlows);

3- Carry over the closing balance to the next year's opening balance, create variables that calculate the opening balance for each year as the closing balance of the previous year:

vOpeningBalance2024 = $(vClosingBalance2023);

4- Generalize for Multiple Years

In the data load script, you can use a for loop to dynamically calculate and set the closing balances and the following year's opening balance.

Let vNumYears = 'Number of Years'; // Adjust the total number of years considered for calculation

vYear = 2021 To $(vNumYears) - 1
Let NextYear = $(vYear) + 1;
Let ClosingBalance_$(vYear) = Sum({<Year={$(vYear)}>} NetOpeningBalance) + Sum({<Year={$(vYear)}>} TotalPLOCIChanges) + Sum({<Year={$(vYear)}>} TotalCashFlows);
Let OpeningBalance_$(NextYear) = $(ClosingBalance_$(vYear));

Set Analysis Expression Example:
For your visualization, you can add measures that dynamically reference these variables.

5- Use Variables in Expressions
For example, to calculate the closing balance in a visualization or table:

Sum(vNetOpeningBalance) + Sum(vTotalPLOCIChanges) + Sum(TotalCashFlows)

And for the next year's opening balance field:

IF(Year = 2024, $(vClosingBalance2023),
IF(Year = 2025, $(vClosingBalance2024),
...
...
))

You will dynamically display the closing balance of the year as the opening balance of the next year using the stored variable.


*****Hope these steps will help you to resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.******

[Brip51]

Hi TauseefKhan,

Thank you.  This sounds like a good approach.  I will try this and let you know if I can get it to work.

Regards,

Brian