DAX Balance + Opening issues

[Anonymous]

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?

[Anonymous]

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

    )

VAR LastDateAllCompanies =

    CALCULATETABLE (

        LASTNONBLANK (

            'Calendar'[Date];

            COUNTROWS ( RELATEDTABLE ( FinanceTransactions ) )

        );

        ALL ( FinanceTransactions[CompanyID] )

    )

VAR Result =

    CALCULATE ( [BalanceSum]; LastDateAllCompanies ) + OpeningAllCompanies

RETURN

    Result

I thought this a fairly common scenario in Finance reporting, but is seems to be more complex than I first assumed.