Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
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,
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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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...
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] )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |