Forum Discussion
Titatovenaar2
5 years agoAdvocate II
DAX: Running Total that doesn't Reset, with Inactive Relationship (pbix included)
Hi guys, I try to calculate a running total with an inactive relationship that cumulates the data per month. It should always start from the very first moment there is data available, yet there i...
- Anonymous5 years ago
Yes, this solved the problem because under the hood your DAX is transformed into:
VAR v1 = MAX('DIM Calendar'[Date]) RETURN CALCULATE( [Count New Materials], // This filter overwrites what's // coming from the outside world. FILTER( ALL( 'DIM Calendar'[Date] ), 'DIM Calendar'[Date] <= v1 ), // If your table 'DIM Calendar' is // marked in the model as a date // table, this ALL (in your code) // is not necessary since the engine // performs this line below automatically. ALL('DIM Calendar') )The other one does not work because it's equivalent to this DAX:
var MaxDate = MAX('DIM Calendar'[Date]) return CALCULATE( [Count New Materials], KEEPFILTERS( 'DIM Calendar'[Date] <= MaxDate ), ALL('DIM Calendar') )KEEPFILTERS prevents the expression from reaching rows outside the current filter context and you need to be able to do it to calculate what you want.
Anonymous
5 years agoNot applicable
First, this is incorrect
DATESBETWEEN('DIM Material'[Created On],MIN('DIM Calendar'[Date]),MAX('DIM Calendar'[Date]))as time-intel functions DO NOT WORK on arbitrary date columns. The first argument must be a date column from a proper date table.
Second, if you don't want it to reset, you can't use ALLSELECTED.
Third, ALLEXCEPT is a tricky function and should rarely be used. Please read this to understand its use and consequences. In there you may find a way to make it right with the combination of ALL/VALUES.