Forum Discussion

pxs2021's avatar
pxs2021
New Member
4 years ago

Balances

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?

Need Help

2 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    4 years ago

    Hi pxs2021 

     

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

  • pxs2021's avatar
    pxs2021
    New Member
    4 years ago

    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?