Getting difference between this week's average and last week's average

[Anonymous]

Hi I am new and this is meant to be a very easy task but for some reason I've spent hours on it with no result???

All I want is the difference between this week's average and last week's average.

Sample table:

Weekend	Price
28/12/2019	1
28/12/2019	1
28/12/2019	1
21/12/2019	3
21/12/2019	1
21/12/2019	2
14/12/2019	4
14/12/2019	1
14/12/2019	1
14/12/2019	4

So the answer I am expecting is (1+1+1)/3 - (3+1+2)/3 = -1

I tried to google but came up with rankx and really complicated measures which I don't understand...

Any help would be appreciated

[d_gosbell]

You can break this problem down into smaller pieces using variables. So you can get the current week date, the prior week date and the averages for those two dates.

An expression like the follow will give you the answer you are after based on your sample data

Change from Prior Week Avg = 
var _currentWeek = max('Table'[Weekend])
var _priorWeek = maxx(filter(all('Table'[Weekend]), 'Table'[Weekend] < _currentWeek), 'Table'[Weekend])
var _currentAvg = AVERAGEX(filter(all('Table'), 'Table'[Weekend] = _currentWeek), 'Table'[Price])
var _priorAvg = AVERAGEX(filter(all('Table'), 'Table'[Weekend] = _priorWeek), 'Table'[Price])
return if(ISBLANK(_priorAvg),blank(), _currentAvg - _priorAvg)

[d_gosbell]

You can break this problem down into smaller pieces using variables. So you can get the current week date, the prior week date and the averages for those two dates.

An expression like the follow will give you the answer you are after based on your sample data

Change from Prior Week Avg = 
var _currentWeek = max('Table'[Weekend])
var _priorWeek = maxx(filter(all('Table'[Weekend]), 'Table'[Weekend] < _currentWeek), 'Table'[Weekend])
var _currentAvg = AVERAGEX(filter(all('Table'), 'Table'[Weekend] = _currentWeek), 'Table'[Price])
var _priorAvg = AVERAGEX(filter(all('Table'), 'Table'[Weekend] = _priorWeek), 'Table'[Price])
return if(ISBLANK(_priorAvg),blank(), _currentAvg - _priorAvg)

[d_gosbell]

You can break this problem down into smaller pieces using variables. So you can get the current week date, the prior week date and the averages for those two dates.

An expression like the follow will give you the answer you are after based on your sample data

Change from Prior Week Avg = 
var _currentWeek = max('Table'[Weekend])
var _priorWeek = maxx(filter(all('Table'[Weekend]), 'Table'[Weekend] < _currentWeek), 'Table'[Weekend])
var _currentAvg = AVERAGEX(filter(all('Table'), 'Table'[Weekend] = _currentWeek), 'Table'[Price])
var _priorAvg = AVERAGEX(filter(all('Table'), 'Table'[Weekend] = _priorWeek), 'Table'[Price])
return if(ISBLANK(_priorAvg),blank(), _currentAvg - _priorAvg)

[d_gosbell]

You can break this problem down into smaller pieces using variables. So you can get the current week date, the prior week date and the averages for those two dates.

An expression like the follow will give you the answer you are after based on your sample data

Change from Prior Week Avg = 
var _currentWeek = max('Table'[Weekend])
var _priorWeek = maxx(filter(all('Table'[Weekend]), 'Table'[Weekend] < _currentWeek), 'Table'[Weekend])
var _currentAvg = AVERAGEX(filter(all('Table'), 'Table'[Weekend] = _currentWeek), 'Table'[Price])
var _priorAvg = AVERAGEX(filter(all('Table'), 'Table'[Weekend] = _priorWeek), 'Table'[Price])
return if(ISBLANK(_priorAvg),blank(), _currentAvg - _priorAvg)

[d_gosbell · Accepted solution]

You can break this problem down into a number of smaller steps using variables. So you can calculate the current week date, the prior week date and then the averages for those 2 dates.

The following expression should give you the answer you are after based on your sample data

Change from Prior Week Avg = 
var _currentWeek = max('Table'[Weekend])
var _priorWeek = maxx(filter(all('Table'[Weekend]), 'Table'[Weekend] < _currentWeek), 'Table'[Weekend])
var _currentAvg = AVERAGEX(filter(all('Table'), 'Table'[Weekend] = _currentWeek), 'Table'[Price])
var _priorAvg = AVERAGEX(filter(all('Table'), 'Table'[Weekend] = _priorWeek), 'Table'[Price])
return if(ISBLANK(_priorAvg),blank(), _currentAvg - _priorAvg)