topic Re: Running balance ignoring other columns in DAX Commands and Tips

Running balance ignoring other columns

cdcaruba — Tue, 24 Nov 2020 13:36:55 GMT

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.

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?

Re: Running balance ignoring other columns

CNENFRNL — Tue, 24 Nov 2020 15:44:49 GMT

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] ) )

Re: Running balance ignoring other columns

cdcaruba — Tue, 24 Nov 2020 19:36:08 GMT

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...

Re: Running balance ignoring other columns

cdcaruba — Thu, 26 Nov 2020 21:27:23 GMT

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??

Re: Running balance ignoring other columns

CNENFRNL — Thu, 26 Nov 2020 23:30:58 GMT

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,

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.

Re: Running balance ignoring other columns

cdcaruba — Fri, 27 Nov 2020 14:43:11 GMT

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 )

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.