Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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....
Need Help
  • d_gosbell's avatar
    d_gosbell
    6 years ago

    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's avatar
d_gosbell
Icon for Super User rankSuper User
6 years ago

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)