Balances

[pxs2021]

Hi All,

I am trying to create a formula for opening and closing balances in PowerBI.

I am calculating the opening balance for the quarter (lets say Q4 2021 - which would be 1st Oct 2021 to 31st Dec 2021), however in my formula it won't give me the opening balance from Inception to 1st Oct 2021 (i.e. 'length to date')

This is the formula I am using for opening balance

Opening Cash Balance =

var _value = calculate (sum('Chart of accounts'[Opening Cash Balance due from Issuer SCY]), filter(all('Cashflows V2'),'Cashflows V2'[PostingDate.Date] = max('Cashflows V2'[PostingDate.Date])))

return _value

- 'Chart of accounts'[Opening Cash Balance due from Issuer SCY] is another measure which is summing 10110 Accounts Receivable + 10175  Accrued Interest Receivable

- Closing Balance would be 10110 Accounts Receivable + 10175 Accrued Interest

The formula above seems to be give the ending balance as at 31st Dec, but not the opening balance as on 1st Oct.

Any help writing this please?

[pxs2021]

Thanks @littlemojopuppy  

I can't get the function to work: 

Measure = OPENINGBALANCEQUARTER(sum('Chart of accounts'[Opening Cash Balance due from Issuer SCY]), 'Cashflows V2'[PostingDate.Date],filter(all('Cashflows V2'[PostingDate.Date] = max('Cashflows V2'[PostingDate.Date]))))

Am I missing something here?

[littlemojopuppy]

Hi @pxs2021 

DAX includes functions for OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER and OPENINGBALANCEYEAR (as well as closing balance).  They might be helpful.