Forum Discussion
Calculating Opening and Closing Balance
Hi,
Need your help with calculating opening and closing balance per period that user selects. We have a table as our visualization and columns are VendorName, Balance (DebitAmount - CreditAmount) and possible 2 more columns, at least we had 2 columns, and it's opening balance and closing balance. We used 2 measures (opening and closing balance columns), the formula was : Closing Balance = SUMX(FILTER(VendorLedgerEntries, CALCULATE(MAX(VendorLedgerEntries[PostingDate].[Date]), ALLSELECTED(VendorLedgerEntries))>= VendorLedgerEntries[PostingDate]),VendorLedgerEntries[DebitAmount] - VendorLedgerEntries[CreditAmount])
VendorLedgerEntries is our table and VendorLedgerEntries[PostingDate] are dates of every ledger entry,
User picks a date from slicer, and we put a PostingDate in slicer in DateHierarchy format. Periods are year, quarter and month.
We tried with openingbalancemonth/year/quarter function, but didn't succeed
The problem is when the date is picked, our main table with vendor data is filtering, but it is not supposed to, we need all data to show.
Request is that we calculate starting balance from the beginning of time( in our case it is year 2018) to the first day of selected period, and closing balance is all transactions to the last day of selected period
Sorry for bad English, we'll send more info if you need.
Thank you in advance!
Problem solved!
This function worked for uscalculate( sum(VendorLedgerEntries[DebitAmount]) - sum(VendorLedgerEntries[CreditAmount]), DATESBETWEEN('Calendar'[Date],blank(),min('Calendar'[Date])))
3 Replies
- amitchandak
Super User
Jelena17 , if date table is joined with posting date a measure like
calculate( sum(VendorLedgerEntries[DebitAmount]) - sum(VendorLedgerEntries[CreditAmount]), filter(allselected('Date'), 'Date'[Date] <= Max('Date'[Date])))
Thank you for answering,
This is the output with the measure you sent. Still doesn't work, my guess is because of MIN function, because it can't see the dates before selected date. Example: Selected 2020/1/1, and if we use MIN(selected date), there is no values before 2020/1/1 because our slicer filters the table.
Then we turned off interaction between slicer and table, but it is the same. We also tried getting value from slicer, our expected value is on card visualization (1/1/2020), with selectedvalue function.
Do you have any suggestions about what should we do next?
Again, thank you in advance!
Problem solved!
This function worked for uscalculate( sum(VendorLedgerEntries[DebitAmount]) - sum(VendorLedgerEntries[CreditAmount]), DATESBETWEEN('Calendar'[Date],blank(),min('Calendar'[Date])))
