Create a 445 Calendar using Power Query

March 19, 2015

by Matt Allington

In my last blog on PowerPivotPro.com 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 of this entry »


Create a Custom Calendar in Power Query

February 19, 2015

by Matt Allington

In this post, I am going to show you how simple it is to create a custom calendar using Power Query.  If you follow the steps below, you will get a good sense of how capable Power Query is, and may spark your interest to learn more about it.

Different Types of Calendars

There are lots of different ways of creating a custom calendar for use in your Power Pivot workbooks, and plenty of discussion about them on PowerPivotPro.com (25 prior to this one).  Historically I have just used an Excel workbook that I created with all the relevant columns, and enough rows of dates to cover for the next year.  There is one problem with this approach however –  the date over runs when you are looking at last years’ data in a pivot.

over run

As you can see above, we have dates into the future that are showing sales for last year with no sales this year – because we haven’t had “this year” yet.  It is a pain to change the import filter on your Excel calendar each time you refresh (not practicable actually).  You can write some snappy Excel functions in your source calendar table to code around this, or you could make your DAX more complex, but better still – I think this is a great opportunity to learn some new skills – Power Query.

Building a custom calendar from scratch in Power Query is actually quite straight forward, and I am going to take you through it step by step below.

First Create a New Blank Power Query Workbook.

The Blank Query option is right at the bottom of the “From Other Sources” menu.

blank query

If you haven’t done so already, turn on your Formula bar from the view menu.  You will need this so you can easily see the Power Query Code that is generated by the UI tools, and also to allow you to edit the code by hand when needed.

turn on formula
Read the rest of this entry »


SQL Date Tables in Power Pivot

December 11, 2014

Guest post by Thomas Allan

SQL to Power Pivot

Intro by Avi: As in Excel, in Power Pivot there are often many ways to accomplish the same thing. For example you can source a Date/Calendar table from Excel, Azure (here and here), Power Query, and SQL (this post). That is usually a sign of strength of the tool.  Although it also makes it more challenging/fun to be able to weigh the options and decide which one works best for you given your situation. Thomas, shows us a cool way to pull Date table from SQL.

Stepping back a bit, there is some good interplay between Power Pivot and SQL. In terms of feeding Power Pivot using SQL – see Why PowerPivot is Better Fed From a Database Part 1 and Part 2. And also Power Pivot being a great addition for SQL savvy folks – see I Know SQL Queries, So Why Do I Need Power Pivot?. Goodness all around, I say :-)
Take it away Thomas…

Benefits

In addition to often mentioned benefits of using SQL servers as data stores (flexibility, reliability, scalability and security), the benefit of linking to a centralized source that delivers results quickly, consistently to practically any client, anywhere, adds a powerful dimension of “portability“ from the outside of the Excel workbook as Power Pivot and DAX formulas offer on the inside.

Date Table

Four types of resources are often used to create date tables within Power Pivot: 1) Excel itself, using formulas or VBA, 2) data feeds, which you can find an example of following this hyperlink, 3) Power Query, which you can find an example of following this hyperlink, and 4) relational databases.

The example that follows was developed using the relational database SQL Server 2012 and uses only table-valued functions. Although the code was developed on SQL Server 2012, it was also tested on a 2008 release of Microsoft’s flagship database product.

For demonstration purposes, the solution offered here is based on a calendar fiscal year (quarters start January 1, April 1, July 1 and October 1). For other types of calendars, such as 4-4-5 or school semesters, the code can modified by a SQL developer (also, for other types of calendars, some architectural issues may also apply inside Power Pivot, which are fully explained in Rob Collie’s comprehensive Power Pivot course).

This post assumes that the reader has basic familiarity with SQL Server Management Studio, sufficient to install table-valued functions, or has access to someone who knows how to install table-valued functions. This post also assumes familiarity with connecting to a SQL Server database from within Power Pivot (similar to connecting to an Access database).

Download SQL code below.

Read the rest of this entry »


Week Ending Date Calculation

April 29, 2014

Guess Post by Scott Senkeresty at Tiny Lizard

imageJust a quick and practical tip today.

We have a really typical looking Date table.  However, we are going to be drawing some pretty charts summarized by weeks, and our business defines “end of week” at Saturday.  So, we need a new column in our Date table that stores this “Week Ending” date for each row.

The first thought to occur to me was “well, for each Year&WeekOfYear, I just want to grab the max date”.   That sounded easy enough… EARLIER() no longer scares me…
Read the rest of this entry »


The GFITW “Loses” an ALL()

November 7, 2013

 

Yes, We’ve Seen This Image Before and I Am Sure We Will See it Again

Jump in the Wayback Machine…

In the Spring of 2011, I dove into a Power Pivot project that I thought was going to be simple, but even today remains the most complex thing I’ve ever done in DAX.  I think it’s fair to say that the experience, at the time, was traumatizing.  (The client’s business logic itself was/is incredibly complicated.  It’s 100% legitimate, but I think barometric pressure might be factored into their budget/actuals ratios.  Kidding.)

But like many difficult experiences, a lot of good came of it as well:

  1. I learned a ton – it forced me to advance my Power Pivot knowledge significantly
  2. It demonstrated to me that Power Pivot essentially had no “ceiling” – it could handle almost anything
  3. It became a Microsoft case study
  4. It “spawned” the GFITW.

Ah yes, the Greatest Formula in the World.  The solution to all our custom calendar needs, and a pattern I’ve repeated hundreds of times since.  On the blog, in the book, in client workbooks, everywhere.

Well it turns out, the GFITW could afford to go on a diet.

Um, Yeah.  The First ALL Isn’t Necessary (But Doesn’t Cause Problems)

Here’s the “classic” GFITW pattern:

Read the rest of this entry »


Traveling into the Future: Measuring Things that Lag

October 24, 2013

 
How do we compensate for "lagged" data in Power Pivot?

The Red Bars Are Accurately “Tied” to the Months in Which Those Sales Happened,
but the Blue Bars are Four Months “Late.”

Tales from Real Life

So you know, I wrote a book last year.  Being the author, I get reports on how well it’s selling.

What follows is strictly inevitable, considering the people involved and their addictive relationships with data.

Read the rest of this entry »


Simplifying Time Calculations and the User Experience using Disconnected Slicers

June 25, 2013

Guest Post by Jeff Lingen [LinkedIn]

How does PowerPivot fit in an enterprise BI environment

We don’t even know what it is yet. We don’t know what it is. We don’t know what it can be, we don’t know what it will be, we know that it is cool.

Zuckerberg’s early assessment of Facebook was a lot like how I felt after first discovering PowerPivot 3+ years ago. I knew it was cool but had no idea how it would fit into an enterprise business intelligence environment. For a long time PowerPivot for me was just a cool thing that I used for my own data analysis or for proto-typing tools that I would eventually turn into “enterprise-level” solutions. Today I need a pretty compelling reason not to use PowerPivot for almost all of my organization’s analytic requirements. So where does PowerPivot fit into the enterprise BI environment and how do you get associates engaged and use it to provide value?

Read the rest of this entry »

Counting “Active” Rows in a Time Period: Guest Post from Chris Campbell

April 9, 2013
  • image

  • “There’s a Fight Club up in Delaware City.”

  •     “Yeah, I heard.”

  • “There’s one in Penns Grove too.”

  •     “Bob even found one up in New Castle.”

  • “Did you start that one?”

  •     “No, I thought you did.”

 

 

It Keeps Spreading Smile

About a week ago I was talking to Chris Campbell and some of the other folks at Blue Granite.  Chris mentioned that he has been teaching some PowerPivot classes at the Microsoft Technology Centers, sometimes even in my neck of the woods, but I didn’t know until he told me.

Which, of course, instantly reminded me of the scene above in Fight Club.  I’m sure everyone else makes the same connection right? Smile with tongue out

Anyway, Chris asked if I would be interested in him writing a guest post and I said heck yeah!  So, without further delay, I give you the PowerPivotPro.com debut of Chris Campbell.

-Rob

The Problem

Recently, a customer sent me a question regarding a DAX problem they were working on. They have a Members table in their model that includes attributes of “Start Date” and “End Date” for each member. The question they needed to answer was “How many active members did we have in [fill in the blank]?” I thought this was a pretty interesting question and it seemed like it ought to be pretty easy to do in DAX.

Read the rest of this entry »


Guest Post: Always Show Yesterday, Today, or Tomorrow’s Data

March 28, 2013

 
Today we have a new guest poster – Miguel Escobar.  I’ve been talking to Miguel in email and Skype for a long time now and feel silly that I haven’t asked him to do a guest post until today.  But now, I have, so I can stop feeling silly.

From his writing style and creative approach to solving problems, I think you’ll see that he fits right in.

Cool trick: Always show Yesterday’s, Today’s or Tomorrow’s Data

clip_image002

Executive: Are these values correct?
Excel-guy: yes, but you need to check the dates slicers to see what dates the report is using
Executive: Ugh… I just want to click on the report and see the latest values

If you ever had this situation before let me tell you that you’re not alone on that one…I’ve been there before and it’s time to give you some cool easy tricks on how to set up a Powerpivot report that shows you the yesterday, todays, tomorrow, next week or any type of timeframe (forecasting or that sort of scenario).

Read the rest of this entry »


Modeling Viral and Marketing Growth, Part 3 of 3

January 24, 2013

Why am I doing this in PowerPivot?  Primarily as a challenge.

This is a question I should have answered before I even started down this road.

To be honest, I did it primarily as a challenge – to stretch my brain a little bit.  If I were faced with this exact same task in my daily work, undoubtedly I would just use normal Excel formulas.  In some ways, this modeling exercise has been a deliberate misuse of PowerPivot.  A handful of parameters with no source data whatsoever – this is NOT what the PowerPivot engine was built for, which explains why the PowerPivot solution is actually significantly more difficult than the Excel solution.

“So you’ve been deliberately wasting our time??”

No, I do think there is real value in this exercise, for two reasons:

  1. Brain-stretching with new techniques always comes in handy later.  For instance, on the first post Sergey commented that he’d been thinking about loan amortization measures and this could be applied to that.
  2. I can see this technique being added, as a supplement, to a broader PowerPivot model.  For instance, a model containing lots of real customer data over time, and then a [Projected Customers] measure that forecasts future customer populations based on various assumptions and/or marketing investments.

So with that in mind, here it is:  the final installment of viral/marketing modeling in PowerPivot.

Read the rest of this entry »


Modeling Viral and Marketing Growth, Part Two

January 22, 2013

 
Picking up from last week’s post, the first thing I want to show is that I kinda cheated last time.  To see what I mean, let’s look at Rahul’s original chart:

Viral Marketing Growth in PowerPivot:  Customers Flatten Out Over Time

In Rahul’s Viral Model, Total Customers “Goes Flat” Quickly

In Rahul’s model, if we start With 5,000 initial customers and a viral factor of 0.2, we end up with 6,250 customers and we never get any more!

But in my model from last week, if I use 5,000 and 0.2, customers keep piling up exponentially:

Exponential Ongoing Viral Growth in PowerPivot

In My Model from Last Week, Customers Never Go Flat –
They Just Keep Growing Exponentially

So why the difference?

Read the rest of this entry »


NETWORKDAYS() Equivalent in PowerPivot?

November 22, 2012

 
There is no NETWORKDAYS() Function in PowerPivot

There is no NETWORKDAYS() Function in PowerPivot

A Post on Thanksgiving?

Normally I would take today off and not have a post.  But I’ve posted so many updates about the book lately that it’s got to feel like this place has turned into an advertisement shop, and I want to keep the “real” content up.

So consider this a Thanksgiving “bonus” post – me giving thanks for everyone ordering the book, and everyone tolerating my desire to post updates about “my baby” every five minutes Smile

A Missing Function

It’s funny, I’ve never used NETWORKDAYS() much (if at all) in regular Excel, so I didn’t realize this until someone posted on the MrExcel forums – how do I do a NETWORKDAYS()-style calculated column?

Something like this:

NETWORKDAYS in PowerPivot

Desired Result

So how do we get to this?

Read the rest of this entry »