Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX Balance + Opening issues

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...
DAX
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.