PowerPivot provides a host of great functions like DATESYTD, DATESMTD, DATESBETWEEN, etc. that are useful for calculating many things, including a running total.
But with the exception of DATESBETWEEN, I seldom get to use any of those “time intelligence” functions, for the simple reason that our clients almost never operate on a traditional calendar.
I very often find myself working with a “calendar” that looks like this:
Can’t Use Time Intelligence Functions With a Calendar Like This
And sometimes I even find myself with “calendars” that don’t have any date columns in them at all:
This Table DOES Truly Represent the Business Calendar But Has No Dates in It!
So what do you do when you still need a “Year to Date” Total?
Silly humans. They don’t care that the data is structured one way or another under the hood. They just keep insisting on seeing useful things, like Year to Date totals. They don’t want to hear how the blender is constructed, they just want their daiquiri.
So, what’s a report designer to do? Give up? No way. We make daiquiris anyway.
I’m going to use that second calendar above, the one that has no dates in it. In fact this is the same data set I have been using for the Precedence Project. (Really, this is Part Three in disguise). So let’s return to a familiar pivot:
Familiar Starting Point
It has Year and MonthNum on rows, and the simple measure Total Sales. I want to end up with this:
Getting to that desired result, in my experience, is something you either stumble upon quickly or flail around forever and never find. In fact, finding myself in a situation like this is what triggered me to start the Precedence Project in the first place.
Cutting to the chase: this formula works
To keep this post short and sweet, I’m just going to share a working formula. I hesitate to call it “the” formula, because there are multiple variations that work, and some fit certain biz requirements better than others.
So here is one that works. It has some quirks that I will iron out in the next post.
In short, this measure clears all filters on the entire Periods table, then adds back two filters – restores the current context for Year (using VALUES), and limits the PeriodNum to be less than or equal to the current context (I will explain later why I used MAX and not just VALUES). Note that I am using PeriodNum (which keeps increasing across years and is unique to a given Month/Year combo), not MonthNum (which is on the pivot and “resets” to 1 for January of each year).
Next Post: Explaining the Formula, and Showing How NOT to do it
Explaining that relatively simple formula, if I do a thorough job, will consume its own post. And I also want to cover some other approaches – ones that seem like they should work but do not. Because the things that DON’T work are even more educational than the things that do.