Forum Discussion
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 is still a period selector to select the specific From date and To date simultaneously.
Currently my main measure on which I apply the Running Total is the following:
Count New Materials =
CALCULATE(
DISTINCTCOUNT('DIM Material'[%MaterialKey]),
DATESBETWEEN('DIM Material'[Created On],MIN('DIM Calendar'[Date]),MAX('DIM Calendar'[Date]))
)
And the running total measure I use is the following:
Count New Materials RT =
VAR CumulativeValues =
CALCULATE(
[Count New Materials]
,FILTER(
ALLSELECTED('DIM Calendar'[Date]),
'DIM Calendar'[Date] <= MAX('DIM Calendar'[Date])
)
--,Maybe add here a USERELATIONSHIP?
,ALLEXCEPT ( 'DIM Calendar', 'DIM Calendar'[Month])
)
RETURN
IF( ISBLANK( [Count New Materials] ) ,
BLANK() ,
CumulativeValues )
The data that I currently get:
Data that I expect:
And I should be able to select via the period selector a specific period, for instance if I would select: 1/1/2018 - 12/31/2019, I would expect the following to show. So the cumulative does not reset itself.
Any suggestions how I could solve this?
Kind regards,
Igor
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.
3 Replies
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.
Interesting stuff, reading through it to get a better understanding. Thanks.
Meanwhile this somehow solved the problem:
VAR v1 = MAX('DIM Calendar'[Date]) RETURN CALCULATE( [Count New Materials] ,'DIM Calendar'[Date] <= v1 ,ALL('DIM Calendar') ) /* --while the following does not work: CALCULATE( [Count New Materials] ,FILTER('DIM Calendar', 'DIM Calendar'[Date] <= MAX('DIM Calendar'[Date])) ,ALL('DIM Calendar') ) */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.
