Cumulative Totals not working

DAX DAX Calculations
1

My Cumulative total is not changing at all. It only shows me the amount for that transaction.

I’m using Sam’s Date table and has marked the Date table as the Date Table.

I have a Customer Transactions table with a TransDate (transaction date), an amount and “DrCr” indicating whether the amount is positive or negative. The measure [Signed Amount] set the amount accordingly.

I’m trying to show the running total for a particular customer in the report.

Attached my report as well.

test cumulative.pbix (176.2 KB)

2

Hi @fanie,

Welcome to the forum, it’s great to have you here.

There’s actually nothing wrong with your calculation - the problem you’re facing is one of context. As you can see on the image below, when the context is changed everything adds up nicely.

image
image1074×741 57.3 KB

Added a Running Total v2 measure to return a value for all dates in between TransDate dates.

Running Total v2 = 
VAR myTable =
    ADDCOLUMNS(
        DATESBETWEEN(
            Dates[Date],
            CALCULATE( FIRSTDATE( Dates[Date] ), ALLSELECTED( Dates[Date] )),
            MAX( Dates[Date] )
        ),
        "Value", [Signed Amount]
)

VAR Result =
    SUMX (
        myTable,
        [Value]
)

RETURN Result

Here’s more on context:
https://forum.enterprisedna.co/t/context-what-is-this/4385

https://forum.enterprisedna.co/t/evaluation-or-initial-context/4890

https://forum.enterprisedna.co/t/aggregations-or-filter-context/4891

https://forum.enterprisedna.co/t/iterations-or-row-context/4892
.
I hope this is heplful.
test cumulative.pbix (180.7 KB)

1 Like
3

Hi @fanie, we’ve noticed that no response has been received from you since the 8th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

4

Melissa

Thanks for the response and help.

I’ve watched Sam’s video “Customer Deep Dive - Development” again and noticed he starts with the Dates[Date] as the date in his visual instead of the TransDate I’ve used in mine. I changed the date and that solved my problem!

I’ll work through your solution as well to understand the inner workings of Power BI.

5

Nice one!

That’s exactly what I did but I guess I should have described it more clearly, apologies for that.

6

Hi @fanie , you may also want to check this video where Sam talked about cumulative totals. You might get additional tips here.