Cumulative Totals showing same value for each row

[jwolovitz]

Hi all,

I hope this is not a basic question, but I am new to PowerBI, and I've been looking for a long time with no solutions here.

My problem is as follows:
I have a number of accounts (10 or so) for which I have transactional data stored in a General Ledger table [PostGL]. The table has the the following fields (actual field names in parenthesis):

• Account (AccountLink (FK))
• Transaction Date (txDate)
• Debit Amount (Debit)
• Credit Amount (Credit)

I also have an Accounts table with the following fields:

• AccountLink (PK))
• GL Account Number (Account)
• Account Name (Description)

In my model I have created a bi-directional link between 'PostGL'[AccountLink] and 'Accounts'[AccountLink].

I am trying to build a table which shows the cumulative totals for each day of the current month. I have built a measure which sums the account balance as follows:

Account Balance = 
SUM('PostGL'[Debit]) - SUM('PostGL'[Credit])

and a second measure which calculates (or is supposed to calculate) the cumulative balance as follows:

Account Balance Cumulative = 
VAR MaxDate = today() 
RETURN
    CALCULATE (
        [Account Balance],
        PostGL[TxDate] <= MaxDate,
        ALL( PostGL[TxDate] )
    )

This works to the extent that when I build a visual, the correct account balance per account can be seen, however this amount is shown against each date, and I am at a loss as to how to show the account balance as at that specific date.

In otherwords I need this

to be this:

Not sure what in the world I'm doing wrong and would appreciate any assistance.

Many thanks in advance

[d_gosbell · Accepted solution]

You are seeing this behaviour because you are using Today() in your MaxDate variable. You are effectively using the same date for every column which is why you are seeing the same value in every row.

I think if you change this to the following your calc should work:

VAR MaxDate = MAX( 'PostGL'[txDate] )

[jwolovitz]

Thank you so much! This fixed it