Having trouble getting a previous period calculation - help?

[sherifffruitfly]

hi all,

i have a powerpivot table Sales with a column Week (just an integer, counting up from when I started keeping track of data), and a column SalesAmount.

there aren't any dates, just the incrementing week number.

i have a measure that sums over SalesAmount - this let's me look at any slicer-ed week's sales in my pivot table.

i would like another measure that gives the SalesAmount for the prior week (i.e. those rows whose Week number is 1 less than the current row's).

googling around, i ended up trying this:

1) create a new table WeeksTable with a column Weeks, with values just being incrementing whole numbers

2) back in the Sales table, add a new column SalesPrevWeek, defined by the formula:

=CALCULATE(sum([SalesAmount]), WeeksTable[WeekNumber] - 1)

(the sum is there because powerpivot didn't recognize the column without it, and googling suggested summing or otherwise aggregating)


3) on the Sales table, create a measure summing over SalesPrevWeek

this seems to not give me previous weeks' sales amounts - rather it shows current week totals.

what am i missing here?

thanks for any tips,
sff

 

[miguel.escobar]

you almost had it!

=CALCULATE(sum([SalesAmount]), FILTER( ALL(WeeksTable), WeeksTable[WeekNumber] - 1 = MAX( WeeksTable[WeekNumber] ) )

TRY IT!!!