Published April 19, 2013

The Powerpivot Way – CALENDAR TABLE (Monthly and Weekly Totals)

Power Pivot

New Video Up! have you ever wanted to group your aggregations by Month or Week? Check out this video where I use a calendar table to help me make things easier.

Power Pivot
Notify of
5 Comentario
Newest Most Voted
Inline Feedbacks
View all comments
Siraj Samsudeen

Hi Miguel, Your example is a very good illustration of how PowerPivot can make generation of these kind of reports formula-free for the average XL user. Well done!

Your video was getting cut-off at the bottom – is it due to a zoom-setting? Also, for the date filtering, why did you not show the date sliding filter in Excel 2013?

One thing that I would have done anytime I generate a pivot table like this is to apply conditional formatting -> Data bars to show a visual impression of how big the numbers are relative to one another.


I try to keep these videos as short as possible taking into account that regular viewers of these type of videos only watch it from 4 to 6 minutes top and if it’s something of more length then it’s expected to have more content. Looking in retrospective, I’d have put the conditional formatting and the timeline slicers and even showed more rows in the spreadsheeet but you can just be sure that the upcoming videos will be better (:

Siraj Samsudeen

Yes Miguel – you are absolutely correct. I would think twice about watching any video beyond 5 minutes. But with a bit of editing, you could put these things in under 5 minutes.


how to use opening balance of month?

Miguel Escobar

I don’t have an article on that (yet), but the microsoft help article actually brings a really cool example

Also, here’s some patterns on how to accomplish what you need with some simple CALCULATE tricks