Create a 445 Calendar using Power Query

by Matt Allington

In my last blog on I showed how easy it is to create a standard calendar for Power Pivot using Power Query.  Most of my customers however don’t use a standard calendar but instead use a 445 calendar (which is very common in the Retail Industry). A lot of people shared with me their 445 calendars after my last post, and that reminded me that although the concept of a 445 calendar is very common, everyone seems to have different rules on how the calendar works.

Differences in 445 Calendars include

  1. What month you start the financial year
  2. What day of the week is the start of the week.
  3. 365 divided by 7 = 52 + 1 day remainder. Different companies handle the extra day in different ways.

So there are almost as many permutations as companies and I don’t want to go down the path of trying to write 1 calendar that will work for everyone.  Instead this post covers the techniques I used to solve the 445 calendar problem for one of my customers.  If you are so inclined, you can copy these techniques plus some of your own to meet your own 445 needs.  To give you an idea, it took me about an hour to think through the problems, research the functions and build a working calendar.  If you copy my techniques, you should get a head start on that for your own 445.

The rules of this particular calendar are

  1. The first day of the financial year is the Monday on or before 1 May.
  2. The number of weeks each month are in the pattern 454 (4 for May, 5 for June, 4 for July) and then repeats.
  3. The calendar weeks, months and years mirror the financial data.  So Mon 31 Dec 2013 is the first week of financial week 36.  Even though the date is actually in the year 2013, it is treated as part of the first calendar week of 2014.

Here is how I did it.  But first a word of warning.  The steps are easiest to follow if you first download the sample workbook (link at the bottom) and step through the Applied Steps as you read my explanations in this post.  If you are not a Power Query expert and you just read the post, it will quite hard to understand.  So do yourself a favour and download the workbook, and step through it as you read the post

Read the Rest

What is Power Pivot’s #1 Competitor?

Tableau Versus Excel.  Not Tableau versus Power Pivot.  That is telling, ye?

This Picture is a Hint.  An Admittedly Annoying Hint That Hounds me on Facebook.

“OK, way to make it super-obvious, Rob.  It’s Tableau, right?”

Actually, no.  It’s not Tableau.  And the Tableau advertisement above basically proves my point.

By far, the biggest “competitor” to Power Pivot is…  Excel itself. 

In other words, lack of awareness that Power Pivot even EXISTS is still the biggest “competitor” to Power Pivot today.

The Tableau marketing department is smart.  They know that “normal” Excel is their chief competitor.  And they know that “normal” Excel has some frustrating weaknesses when it comes to data analysis.

So they go right for the throat.  I salute and admire their savvy.  Which brings me to a movie quote.

Read the Rest


Guest post by David Churchward [Twitter]


Gantt Hours measure debugging with DAX Studio – isn’t it pretty!

At the end of my last post in the Gantt Chart series, GANTT CHART WITH RESOURCE LOADING, I said that I would return to explain how the Hours measure worked.  I took a brief diversion, almost foray, into the world of CRM PIPELINE FUNNEL CHARTS but I’m now back to explain the workings of that Hours measure.  You can revisit PART1 and PART2 of this series to recap.

In the interim, I’ve been working on some “fine tuning” of that Hours measure.  I’ll come onto the final solution in due course, but I’ll work through the workings of the Hours measure as we left it initially to explain why it needed tuning up!

For those amongst you with an appreciation of SQL, you might think of this like creating a DAX Equivalent LEFT JOIN between tables with a BETWEEN thrown in for good measure.

Read the Rest

CRM Pipeline Funnel Chart (and something on Themes)

Guest post by David Churchward [Twitter]

Pipeline Funnel Chart in Excel PowerPivot

Pipeline Funnel Chart

For those of you waiting on the explanation of the SUMX / SUMMARIZE measure in GANTT POST 2, I have to put you on hold for a while longer.  Sorry.  In truth, I’m working through some performance aspects with that measure.

For now, I thought I would take the opportunity to expose the Pipeline Funnel Chart.  It’s really quite straight forward but it’s incredible how few people know that it’s available to everyone, doesn’t require any real manipulation and YES it’s a standard PowerPivot chart!

For those of you that want to dig straight in, YOU CAN DOWNLOAD THE WORKBOOK HERE

Read the Rest

Gantt Chart with Resource Loading

Guest post by David Churchward [Twitter]

Gantt Chart with Resource Loading Report

Gantt Chart with Resource Loading Report

The response to POST 1 of this Gantt Chart mini series was swift and it didn’t take long for Vegard to contact me with a query regarding resource loading.  Vegard had quickly taken the template and adapted it to include resource hours.  The issue existed with getting the total hours for each resource to behave the way he wanted.  Naturally, individuals will normally work for a set expected amount of time per day and if tasks are planned which exceed that expectation, it’s obvious that they might not get the work done!

In this post, I’ll start to explain how to adapt the model from the previous post to include resource loading and associated subtotals per individual resource.

Read the Rest

Gantt Chart in PowerPivot

Guest post by David Churchward [Twitter]

Gantt Chart in PowerPivot

Gantt Chart in PowerPivot, fully dynamic and sliceable!

It’s a rare diversion from the normal Financial stuff that I subject you to!  Having run numerous projects and found myself writing endless task lists in Excel so that I can distribute to other people, it suddenly occurred to me that PowerPivot can do this.

Not only can PowerPivot do Gantt Charts, I think it destroys other software in this game.  Sure, MS Project is always going to be there and I’m very fond of it, but, anyone familiar with this:

“Thanks for sending that through, but I don’t have MS Project.  Can you give me an Excel version or PDF”?”

Maybe you haven’t, but I find myself continuously confronted with distributing Excel task lists and producing my own Gantt charts in Excel to grab screenshots for presentations.  PowerPivot gives me (and you) the means to monitor and update tasks quickly and easily, but it also gives you the means to distribute via Sharepoint – awesome!

If you want to just get on with it, GRAB THE FILE HERE and start using it.  There’s some brief instructions on how to use it in the file.  Otherwise, read on …

Read the Rest

Tiered Commissions, Income Taxes, and other “Tiered Rate” Calculations in PowerPivot

Guest post by David Churchward [Twitter]

Commissions Part 3 Report

I mentioned in my LAST POST that I would return with a variation on that commission calculation.  Two months later and very much overdue, here it is!  Apologies to those of you who have been waiting on me for this.

I left off with a calculation that applied a commission rate based on a rates table.  That rate was applied to the total value.

But what about a scheme where one rate applies to the first N dollars (or pounds, euros, etc.), then another rate applies for the next M dollars, and so on?  Income taxes are calculated this way, for instance.  And being able to solve this sort of thing in Excel very efficiently (using PowerPivot) will be generally quite useful.

In this post, I’ll explain how to do this in a model that I refer to as Base Plus.  And while I will discuss this purely in the context of tiered commissions, keep in mind that it applies to taxes and many other things as well.

Read the Rest

Commission Calculations in PowerPivot Part 2

Guest post by David Churchward [Twitter]

Team and Manager Commissions Report

You may recall in my last post, COMMISSION CALCULATIONS IN POWERPIVOT, we got to the point where we could dynamically calculate the sales value and attributable commission rate that should be applied based on time, value and team parameters, reading from a Rates table.

In this post, we’ll complete the commission calculation, providing a different value for individuals and the team manager.

Where are we?

Just to recap, we got to the point in my last post where we had calculated [Sales_Value] and [Comm_Rate] as below:

Read the Rest

Commission Calculations in PowerPivot

Guest post by David Churchward [Twitter]

Commissions Report

Firstly, I have to be clear that I’m not presenting a “one-size-fits-all” approach to sales commission calculations here.  That wouldn’t be possible because commission schemes vary extensively and, in my experience, some schemes aren’t even based on a logic that can derive a mathematical answer!  However, I’m presenting an approach here that will hopefully provide a template approach that can be modified to a number of different scenarios.

The Target Outcome

The scheme that I’m using in this example operates as follows:

  1. Commission is paid monthly based on the achievement in that month
  2. As a salesperson sells more, then accelerators trigger.  That is to say, for example, a salesperson may receive 1% of sales up to £10,000 and 2% of sales between £10,000 and £20,000.  The 2% is payable on the whole value.  Therefore, if a salesperson sells £11,000 then they would receive 2% of the full £11,000
  3. Percentages and bands can change monthly at management discretion
  4. This is a monthly threshold so each salesperson is reset to zero at the start of each month.
  5. Each product group carries a separate set of bands and rates
  6. Managers receive commission at a different rate to the sales team based on the total sales for their team.
Read the Rest