DAX: Running Total that doesn't Reset, with Inactive Relationship (pbix included)

[Titatovenaar2]

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.

PBIX file 

Any suggestions how I could solve this?

Kind regards,

Igor

[Titatovenaar2]

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')
    )
*/

[Anonymous · Accepted solution]

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]

Hi @Titatovenaar2 

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.