Rolling average producing a tail

[AltGr9]

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(

        DATESBETWEEN(

            'Dimension Date'[Date],

            DATEADD(__LAST_DATE, -29, DAY),

            __LAST_DATE )

       , CALCULATE(SUM('Fact Daily Readings'[Amount]))

    )

My problem is that it is producing a 'tail' in the data.

The [Amount] data for the following contract only goes up to 31 March, but when I include the rolling average measure in the table, it produces blanks for the 30 days after 31 March.

Can anyone help remove this tail in the measure itself, without using slicers?

[Anonymous · Accepted solution]

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

[az38]

Hi @AltGr9 

its unclear your desired output

[Amount] is your stored data from data model as I understand. So, if [Amount] is empty for april dates it is empty in visual

[30 Day Rolling Average] is a measure that calculates average for last 30 day. for example for 15th April it will sum up all the date from 16th March and 15 blank values from April and give you an average.

What ttail do you want to cut?

If you need only March average without April, you can try a measure like

30 Day Rolling Average =
VAR __LAST_DATE = IF(MONTH('Dimension Date'[Date])>3, DATE(2013,3,31), LASTDATE('Dimension Date'[Date]))
RETURN
    AVERAGEX(
        DATESBETWEEN(
            'Dimension Date'[Date],
            DATEADD(__LAST_DATE, -29, DAY),
            __LAST_DATE )
       , CALCULATE(SUM('Fact Daily Readings'[Amount]))
    )