cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Running balance ignoring other columns

Hi, 

I am new to DAX and have been looking for an answer for about a week now and I cant get it right. 

The scenario is simple. I need a running total to create a balance that keeps on running, independant of other columns. So it just has to keep adding the previous row to the current row. The problem is with the product category column, that creates a group on which the RT is based on. I don't want that. 

 

cdcaruba_0-1606224619938.png

the DAX for "cumulative balance" = 

cumulative balance:=CALCULATE([TotalRevenue],
filter(ALLEXCEPT(dDate,dDate[Year],dDate[Month Number]),
dDate[Date] <= MAX(dDate[Date])
))

 

The data model is classic star schema with Sales fact , date dimension, product dimension. 

How to ignore the product dimension and have the running total keep on totalling? 

 

5 REPLIES 5
Regular Visitor

@CNENFRNL 

I am beginning to understand a bit better. When you say 'cell' do you mean 'row'? (As this is in Excel and a cell is really just the one cell). 

 

Lets concider a slightly different table: in stead op category we use ProductID

 

Perhaps a relationship can be created by concatenating the productID and the YearMonth column, creating a YearMonthProductID column (but I dont want to show it in the results)?

This is almost it, but obviously it resets at the next month.

cumulative balance:=var maxProdID = MAX( dProduct[ProductID] )
RETURN
    CALCULATE ( [TotalRevenue], dProduct[ProductID] <= maxProdID )

cdcaruba_1-1606486411895.png

I have the feeling all will be soleved if it was possible to create a dynamic rownumber for the whole table, but I haven't been able to figure that out. 

 

 

Regular Visitor

Managed to fix the formula provided bij @CNENFRNL with a var

 

cumulative balance :=
var maxDate = MAX( dDate[Date] )
CALCULATE (
    [TotalRevenue],
    dDate[Date] <= maxDate,
    ALLEXCEPT ( dDate, dDate[Year], dDate[Month Number] )
)

 

, but the result is still the same. 

What I am I missing here??

Hi, @cdcaruba , my bad, glad that you worked it out; but after a closer look at it, I think the measure can be authored this way,

cumulative balance :=
VAR maxDate = MAX ( dDate[Date] )
RETURN
    CALCULATE ( [TotalRevenue], dDate[Date] <= maxDate )

 

As to your question, I think you missed a very fundamental concept of DAX, evaluation context. Take a look at the illustration first,

cdcaruba_0-1606224619938.png

 

The most general rule for any DAX measures, a measure evaluates cell by cell; every cell has its own evaluation context for a measure.

Cell 1: Dates[Year]=2017, Dates[Month Number]=1, Product[ProductCategory]="Advanced"

Cell 2: Dates[Year]=2017, Dates[Month Number]=1, Product[ProductCategory]="Beginner"

What's the logical relationship between these two categories for accumulation? Then with other items in the category? As long as you can describe such a logic, you can author a DAX for accumulation.

Regular Visitor

Hi @CNENFRNL , yes I actually tried that. Getting a calculation error: 

A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I am doing this in Excel by the way...

Super User III
Super User III

Hi, @cdcaruba , it seems you're intended to apply filters this way,

cumulative balance :=
CALCULATE (
    [TotalRevenue],
    dDate[Date] <= MAX ( dDate[Date] ),
    ALLEXCEPT ( dDate, dDate[Year], dDate[Month Number] )
)

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.