Here’s a post for the Excel nerds out there, because this was so helpful to me over the past couple of days that it is worth talking about. For non-Excel nerds, if you work in analysis or reporting, give this post a quick glance at least, you might pick up something useful!
Recently I was tasked with adding month to date and year to date outputs to a weekly report. Initially the request was only for the year to date, which is easy, you just do the start of the year up to the report date, but things have moved on and month to date is a little bit more of a pain if you don’t want loads of worksheets or custom measures/grouping in pivot tables.
Over the past few days, I’ve been working on a flexible way of joining and referencing all of the data so that any conceivable date range can be calculated without adding more groupings or specified ranges to the data. This means that you’ll be able to compare fortnightly results, or last 3 days, or rolling 12 months, whatever takes your fancy. The other benefit is that once this has been done once, it only gets easier. In time I could have all of the heavy lifting in Excel done behind the scenes in a completely traceable workflow with fewer mistakes than manual work or even VBA.
So without further ado, lets dig into the mucky underside of Excel and unearth some gems!
As you may know if you follow my posts, I’ve been developing quite a strong crush on PowerQuery, thanks to its ability to clean up even the nastiest looking data inputs, and transform in a similar way to SQL queries, merging, appending, filtering, it’s great fun really!
PowerPivot is one of PowerQuery’s buddies, and they work really well together. Whenever you create a table, be it a run-of-the-mill Excel table or a pivot table, you also have an option to Add to the Data Model. The Data Model is basically PowerPivot and it lets you set up relationships between tables that have been added to the Data Model as well as adding calculated measures and a bunch of other database-y stuff.
Being an odd sort of fellow, I wanted to get data from PowerPivot without a pivot table in my report, because frankly, pivot tables just format horribly and ruin worksheet structures. So I had the option to reference the table with the GETPIVOTDATA formula Excel handily gives you when you try to reference anything in a pivot table, or I can use its prettier cousin, CUBEVALUE.
So I went with CUBEVALUEs, which I was taught over 4 years ago, barely remembered and barely used at the time. The ony thing I did remember was that you can easily convert a GETPIVOTDATA formula into a CUBEVALUE formula, so that’s a great place to start.
The tricky part comes when trying to reference a range, because we’re not creating groups of dates in PowerPivot, that’s not as flexible as I want. Luckily, CUBESET comes to the rescue by letting you define a group of options, or a “set”, within your CUBE formulas!
As a final point, a common question on ranges is how to achieve less than or greater than with CUBE formulas. This is where CUBERANKEDMEMBER comes in, by allowing you to specify to grab the very first item in a group (such as dates) you can specify the date range up to that point. That covers less than, but greater than is a little trickier. To achieve greater than, you need to specify CUBESET and then CUBESETCOUNT, then wrap it all up in a CUBERANKEDMEMBER, because obviously the final count is going to be the last item in your set!
Now I haven’t given any examples here, because I want to keep this post reasonable in length and also because I just wanted to share the things you should be searching for and let you get on with it yourself. You’ll learn a lot more by reading from multiple sources than you would from one guy who gets a little too excited about Excel sometimes.
Discovering and utilising CUBE formulas, PowerPivot and PowerQuery really ups your Excel game, allowing you to throw together custom views of data in a fraction of the time that using INDEX MATCH, VLOOKUP, SUMIFS and custom columns would. Not only that, but it also offers structured, repeatable workflows where you can walk through every single step you took and see how all the data is linked. Even from the worksheet side, it soon becomes clear what’s going on with the CUBE formulas.
And if you really want some clear advice on PowerPivot, maybe try the book by the guys at the website powerpivotpro.com. I’ve been on that site multiple times and their advice is really easy to follow.
Books mentioned in this post:
Power Pivot and Power BI: The Excel User’s Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016