cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Titatovenaar2
Helper I
Helper I

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:

Titatovenaar2_0-1620644137914.png

Data that I expect:

Titatovenaar2_2-1620644254169.png

 

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.

Titatovenaar2_3-1620644313828.png

 

PBIX file 

 

Any suggestions how I could solve this?

 

Kind regards,

Igor

 

 

 

 

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

View solution in original post

3 REPLIES 3
Titatovenaar2
Helper I
Helper I

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.

View solution in original post

daxer
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors