Forum Discussion
AltGr9
Helper I
6 years agoRolling average producing a tail
I have a 30 day rolling average measure that works fine, against my [Amount] field: 30 Day Rolling Average = VAR __LAST_DATE = LASTDATE('Dimension Date'[Date]) RETURN AVERAGEX( D...
- Anonymous6 years ago
Here's your measure:
// 1st basic measure [Total Amount] = SUM ( 'Fact Daily Readings'[Amount] ) [30 Day Rolling Average] = var __lastDayWithAmount = lastnonblank( ALL( 'Dimension Date'[Date] ), [Total Amount] ) VAR __lastDayVisible = LASTDATE ( 'Dimension Date'[Date] ) var __shouldCalculate = __lastDayVisible <= __lastDayWithAmount var __startDay = // YOU SHOULD BE CAREFUL WITH THIS // SINCE THIS OPERATION MIGHT GET YOU // OFF THE LEFT BOUNDARY OF THE CALENDAR. // You need to check if this date is // not BLANK. If it is, it means you're // off the calendar... and can't calculate // your measure. DATEADD ( __lastDayVisible, -29, DAY ) var __periodToAverageOver = DATESBETWEEN ( 'Dimension Date'[Date], __startDay, __lastDayVisible ) var __result = AVERAGEX ( __periodToAverageOver, [Total Amount] ) return If( __shouldCalculate, __result )This should work. If it doesn't then some small modifications might be needed.
Best
D
Anonymous
6 years agoNot applicable
Here's your measure:
// 1st basic measure
[Total Amount] =
SUM ( 'Fact Daily Readings'[Amount] )
[30 Day Rolling Average] =
var __lastDayWithAmount =
lastnonblank(
ALL( 'Dimension Date'[Date] ),
[Total Amount]
)
VAR __lastDayVisible =
LASTDATE ( 'Dimension Date'[Date] )
var __shouldCalculate =
__lastDayVisible <= __lastDayWithAmount
var __startDay =
// YOU SHOULD BE CAREFUL WITH THIS
// SINCE THIS OPERATION MIGHT GET YOU
// OFF THE LEFT BOUNDARY OF THE CALENDAR.
// You need to check if this date is
// not BLANK. If it is, it means you're
// off the calendar... and can't calculate
// your measure.
DATEADD ( __lastDayVisible, -29, DAY )
var __periodToAverageOver =
DATESBETWEEN (
'Dimension Date'[Date],
__startDay,
__lastDayVisible
)
var __result =
AVERAGEX (
__periodToAverageOver,
[Total Amount]
)
return
If( __shouldCalculate, __result )
This should work. If it doesn't then some small modifications might be needed.
Best
D