Running totals in table with memo of transactions

DAX DAX Calculations
1

Hello all,
I faced with cumulative totals issue. I have a report from my Balance Sheet. I want to show info in a table: Data, Memo (description of transactions), amount and running balance.
Memo is an always unique text line for every transaction.

I used pattern formula with running totals and it works perfectly without memo column:

Screenshot_1
Screenshot_1558×602 27.9 KB

When I add the Memo column, it doesn’t work:
image

I guess I need to ignore Memo in formula somehow, but I can’t get this. I tried different options. I guess I missed something. Could you please help me with the solution?

running balance in table with memo of transactions.pbix (94.7 KB)

2

Guys, do you have any idea how to solve it?

3

@Pavel ,

If you remove the filters on the memo field, it’s back to working as expected:

Balance = 
// calculate balance on a specific date ignoring all dates filters

IF( [Amount] = BLANK(), BLANK(), 

CALCULATE( [Amount], 
    FILTER( ALL( 'Date'[Date] ),
        'Date'[Date] <= MAX( 'Date'[Date])
    ),
    REMOVEFILTERS( 'sales %'[Memo] )
)
)

image

  • Brian
2 Likes
4

Hi @Pavel, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

5

Thank you Brian. It really helps.
BTW, what if I wanted to have a cumulative balance by transaction? How can we do this?
image

6

@Pavel ,

The way I did this was to create a numeric field called memo number in PQ (basically a 1 to N index), sorted memo by memo number and created the following measure:

Indiv Cumul = 
IF(
    [Amount] = BLANK(),
    BLANK(),
    CALCULATE(
        [Amount],
        FILTER(
            ALL( 'sales %'[Memo Number] ),
            'sales %'[Memo Number] <= MAX( 'sales %'[Memo Number] )
        ),
        REMOVEFILTERS( 'sales %'[Memo] ),
        REMOVEFILTERS( 'Date'[Date] )
    )
)

image

One very subtly tricky thing. Once we sort memo by memo number, we now have to modify our original measure to also REMOVEFILTERS on memo number, even though that field is not present in our visual.

I hope this is helpful. Full solution file attached below.

2 Likes
7

Thank you! It’s a great idea.

1 Like