How to calculate average of a balance at filter date (month-end and balance at previous year-enddate

[NicoAbsil]

Assume y/e is Jun and current reporting or filter date is Oct-23. How do I calculate the simple average of a balance at at Oct-23 and as at Jun-23. I only have month-end values in table. Needs to be dianamic, i.e. if I filter on Sep-23, average to change to values as at Sep-24 and Jun-23 devided by 2.

[Akash_Varuna]

Identify Year-End: Dynamically calculate the closest June date before or equal to the selected month (e.g., jun-23 for oct-23).

Fetch Balances: Get the balance for the selected month (e.g., oct 23) and the most recent year-end (e.g., jun 23).

Calculate Average: Add the two balances and divide by 2.