Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
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)
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)
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)
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)
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |