Forum Discussion

AltGr9's avatar
AltGr9
Icon for Helper I rankHelper I
6 years ago
Solved

Rolling 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...
  • Anonymous's avatar
    Anonymous
    6 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