Closing balance in summary

edited December 2023 in Modeling

Best Answer

  • M.Kierepka

    Hi @Krishnarao,

    There is no easy way, and that's why native way is usually better, but there are few alternatives - generally using Ratio summary. Depending on the exact module structure, I think either 1st or 2nd should work:
    1. Using RANK() to determine what is the last (or first, as solution is similar) period - or maybe you already have that in your SYS module:

    BUT: If you have more levels of summaries, this will not work.

    2. Using TEXT() to get number in as text, then you can get the "closing balance" using "last non-blank", and convert back to number using VALUE():

    As you can see on the screenshot it should work on all levels (so even if you have years, total for all periods etc.), but you might have rare problems with conversion of the numbers to text and vice versa. Notice that in the "ratio" and "1" line items I used the full list, you can use just upper level (so Months dimension), but then it will only work if you don't have other dimensions in the source "Input" line item.