Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi,
I'm building a table where I need to get opening & closing cash balances for each month. My GL_GLPBPOST data table has a field for transaction date and I have a separate Date table too.
I need to see the Opening & closing balances for each month as shown in the following table,
YEAR | MONTH | Opening Cash | Closing Cash |
2020 | Jan | xxx | xxx |
2020 | Feb | xxx | xxx |
...2022 | ...Jan | xxx | xxx |
** Opening Cash amount = closing cash amount of the previous month/ DCRC = debit (00) & Credit (01)
For this, I've created the following query, but it seems not to return the opening balance,
@ravjay , refer these can help
openingbalancemonth, openingbalancequarter, openingbalanceyear - https://youtu.be/6lzYOXI5wfo
closingbalancemonth, closingbalancequarter, closingbalanceyear- https://youtu.be/yPQ9UV37LOU
Hi @amitchandak ,
I tried closingbalancemonth function, but it gives the total of transactions done on the last day of the month as the closing balance.
for example, lets take May 2022,
I need the total of all transactions up to 30th April (from 01/01/1900) as the Opening balance of May ( -1743616.70)
and the total of all transactions up to 31st May (from 01/01/1900) as the Closing balance of May,(1224749.50)
But what I realised is that 'closingbalancemonth' provide only a total of transaction done on 31/05/2022 (last day of the month) (-353,267.28)
formula I used
**GL_GLPOST[TR_AMOUNT] = total of transactions
User | Count |
---|---|
12 | |
12 | |
11 | |
10 | |
9 |
User | Count |
---|---|
29 | |
16 | |
15 | |
13 | |
12 |