DAX Balance + Opening issues

Question

Hi,&nbsp;I am trying to calculate a YTD Balance based on a Finance transactions table. The table contains daily balances for all accounts AND a year opening balance for all accounts for all years. The later part is giving me a headache. My working formula so far:&nbsp;BalanceSum(YTD) = VAR LastDateAllCompanies =&nbsp;&nbsp;&nbsp; CALCULATETABLE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LASTNONBLANK (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'CalendarPosting'[Date];&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNTROWS ( RELATEDTABLE ( FinanceTransactions ) )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL ( FinanceTransactions[CompanyID] )&nbsp;&nbsp;&nbsp; )VAR Result =&nbsp;&nbsp;&nbsp; CALCULATE ( [BalanceSum]; LastDateAllCompanies )RETURN&nbsp;&nbsp;&nbsp; Result&nbsp;To this formula I need to add the opening balances for all accounts for a given year. the Finance transactions table has a true/false column to indicate if its an opening balance.&nbsp;Any suggestions on how to apply this?

anonymous · Answer

I think I am closing in on the solution, but the numbers are still not adding up (but close):&nbsp;BalanceSum(YTD) = VAR OpeningAllCompanies =&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [BalanceSum];&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FIRSTDATE ( 'Calendar'[Date] );&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FinanceTransactions[Opening] = TRUE&nbsp;&nbsp;&nbsp; )VAR LastDateAllCompanies =&nbsp;&nbsp;&nbsp; CALCULATETABLE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LASTNONBLANK (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Calendar'[Date];&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNTROWS ( RELATEDTABLE ( FinanceTransactions ) )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL ( FinanceTransactions[CompanyID] )&nbsp;&nbsp;&nbsp; )VAR Result =&nbsp;&nbsp;&nbsp; CALCULATE ( [BalanceSum]; LastDateAllCompanies ) + OpeningAllCompaniesRETURN&nbsp;&nbsp;&nbsp; Result&nbsp;I thought this a fairly common scenario in Finance reporting, but is seems to be more complex than I first assumed.

Forum Discussion

DAX Balance + Opening issues

1 Reply

Related content

Open Mirroring Issue

Calculate closing balance using opening balance and net cashflow

Inventory and Stock balance calculation

Opening balances for a day, week or month

Weekly opening balance