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 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)