DAX Balance + Opening issues

Anonymous — Thu, 28 Nov 2019 10:41:44 GMT

Hi,

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:

BalanceSum(YTD) =

VAR LastDateAllCompanies =

CALCULATETABLE (

LASTNONBLANK (

'CalendarPosting'[Date];

COUNTROWS ( RELATEDTABLE ( FinanceTransactions ) )

);

ALL ( FinanceTransactions[CompanyID] )

)

VAR Result =

CALCULATE ( [BalanceSum]; LastDateAllCompanies )

RETURN

Result

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.

Any suggestions on how to apply this?

Re: DAX Balance + Opening issues

Anonymous — Fri, 29 Nov 2019 11:52:44 GMT

I think I am closing in on the solution, but the numbers are still not adding up (but close):

BalanceSum(YTD) =

VAR OpeningAllCompanies =

CALCULATE (

[BalanceSum];

FIRSTDATE ( 'Calendar'[Date] );

FinanceTransactions[Opening] = TRUE

)