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 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
Solved! Go to 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.
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.
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.
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |