PowerPivot: Aggregating 'SAMEPERIODLASTYEAR' Sales by Year, Qtr etc

Question

I've created a new measure which uses [TotalSales] and 'SAMEPERIODLASTYEAR' to calculate the previous year's sales, see below:

=CALCULATE([TotalSales], SAMEPERIODLASTYEAR(Dates[Date]))

This all works fine if I create a pivot that displays individual dates (e.g. 01/01/2015) and then the new measure 'previous year sales' value next to it. My problem occurs when I want to change the pivot and display previous year sales by year, quarter or month - with any of these options I get no sales value.

I'm using a 'Dates' table which is linked to the Sales table.

Am I right in thinking I can re-aggregate sales in this way? I have seen an error message which says something about not been able to aggregate a non-contiguous value or date.

I've had a good look to see if anyone else has experienced the same problem, but can't see anything. Any guidance would be helpful.

Regards, Martyn

Answer 1

Yes you can re-aggregate in this way. Your formula is correct would handles the changes to the aggregation level.

I would check that your 'Dates' table is marked as a date table. Ensure that the year, quarter & months are in this date table and not in your Sales table. Make sure that your date table has one record for each day between the beginning of your sales data set and the end. Check behavior in Power View if you are using Excel 2013.