DAX measure to calculate weekly rolling average not working

[PunchBird]

Hi,

I am trying to create a DAX measure that calculates the weekly rolling average but it's not working.

The measure is:

weekly rolav = 
VAR ValueToday = CALCULATE(SUM('Sales'[Total]), LASTDATE('Date'[Date]))
VAR WeekRol =
CALCULATE(
    AVERAGEX( VALUES( 'Date'[Date]), ValueToday),
    DATESINPERIOD( 'Date'[Date], MAX( 'Date'[Date] ), -7, DAY )
)
RETURN
WeekRol

And this is the result I get, it's basically calculating the sum instead of weekly rolling average:

Can anyone indicate what I am doing wrong here?

Thanks!

@parry2k @amitchandak @lbendlin 

[parry2k]

@PunchBird to confirm, on the 3rd of April, the average will go back to the 2nd and 3rd of April, not going to the last month, in other words not prior to the 2nd of April? Correct?

[PunchBird]

@parry2k   my previous question was a bit confusing, my apologies. What I actually needed (next to the rolling average) is to compare the average of a selected period (e.g. last 7 days) with the same period previous, so the average of 9-16 April compared to the average of 1-8 April. I created the following DAX measures: 

Average This Period =

VAR dayCount = COUNT('Date'[Date])

VAR startDate = MAX ( 'Date'[Date] ) - ((1 * dayCount) - 1)

VAR endDate = MAX ( 'Date'[Date] )

VAR thisPeriod = CALCULATE( SUM('A1'[Total]), DATESBETWEEN( 'Date'[Date], startDate, endDate) )

RETURN

DIVIDE(thisPeriod,dayCount,0) 

Average Previous Period =

VAR dayCount = COUNT('Date'[Date])

VAR startDate = MAX ( 'Date'[Date] ) - ((2 * dayCount) - 1)

VAR endDate = MAX ( 'Date'[Date] ) - (1 * dayCount)

VAR previousPeriod = CALCULATE( SUM('A1'[Total]), DATESBETWEEN( 'Date'[Date], startDate, endDate) )

RETURN

DIVIDE(previousPeriod,dayCount,0)

These measures seem to work when I put the measures each in a Card visual (I checked in Excel and the values are calculated correctly):

The only problem now is when I put these measures in a KPI visual ('this period' in Value and 'previous period' in Target), it displays the value of today and yesterday.... Any idea what's happening here? 

@parry2k

[parry2k · Accepted solution]

@PunchBird I think this is what you are looking for

Weekly Rolling Avg = 
AVERAGEX (
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -7, DAY ),
    CALCULATE ( SUM ( 'Sales'[Total] ) )
)

[PunchBird]

@parry2k one more question - how do I now create a measure that calculates the average of this weekly rolling average for the date period selected? So in the example below, when a date range is selected I would like to calculate the average of the weekly rolling average values of the selected date range.

[PunchBird]

That's what I was looking for indeed, many thanks!