Running balance ignoring other columns

[cdcaruba]

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? 

[cdcaruba]

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

[cdcaruba]

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

[CNENFRNL]

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.

[cdcaruba]

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

[CNENFRNL]

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