Forum Discussion
Getting difference between this week's average and last week's average
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
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)
5 Replies
- d_gosbell
Super User
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) - 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)