Say you have a simple sales table in PowerPivot. What's the easiest way to allow the data to be grouped by week in a PowerPivot pivot table?

I know excel's basic pivot tables have a group feature, but that doesn't seem to apply with PowerPivot.

up vote 2 down vote accepted

The most common approach I've seen is to use a calendar table. Setting up the calendar table can be a little tricky, but once you have it in the data model, join the calendar table to the sales table on the date. Then you can slice and dice and group by date dimensions such as year, month, week, etc.

Here's a good video that walks you through setting up a basic calendar table. https://www.youtube.com/watch?v=c4ezRLg31hc

  • Please include the relevant parts of the video in the answer in case the link breaks. – Ben N Nov 24 '15 at 0:05
=[Date]+7-WEEKDAY([Date],2) 

would be the DAX formula if your week ended on a Sunday

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.