Play Write the greatest song formula in the world, or I’ll eat your souls.”
-From “Tribute,” by Tenacious DAX

### A Do-It-Yourself Time Machine

You remember the Great Precedence Project?  Well, I have shelved it for now, for two reasons:

1. Creating an exhaustive list of all the precedence rules in PowerPivot formulas was going to be…  exhausting.
2. I didn’t really need to boil the philosophical ocean in search of deep underlying meaning, because on further inspection, I already had the Greatest Formula in the World, and needed no other.

So what is the GFITW about?  It’s about time navigation.  It’s useful when you have a custom calendar and the time intelligence functions are therefore not so helpful.

The GFITW is a do-it-yourself time machine.

### Year on Year Sales With a Custom Calendar?  Yes We Can!

In that post linked above, which happened to be the last installment of the precedence project, I was calculating a running total, and doing so without benefit of the fancier functions like DATESYTD etc. (which rely on the “real” calendar, not your business calendar).

OK, so now let’s do a “Year on Year” sales measure, sometimes called a “Year over Year” measure.  And again, our “calendar” table isn’t a true calendar at all:

A Table of Periods (Not Dates) Means You Cannot Use the Built-In
Time Intelligence Functions to Calculate Year-on-Year Sales

And the Sales table is very simple:

Sales Table is Linked to the Periods Table by the Period Num Column

And I have this basic pivot already set up:

Year and MerchPeriod on Rows

So, how do I write a measure that “fetches” the sales from last year?

### Let’s Skip Some Steps

I am going to be honest with you:  it took me a very long time to figure this out.  The formula looks reasonably simple, and it is, but I had to try a million variations before I got it right.  Literally, it took me about a week to refine it.

So rather than walk you through that painful process, let’s skip ahead and show you the GFITW in all its glory.  Then in the next post I can show you what NOT to do, and we can also fine tune it.  OK?  OK.

So here it is, the Greatest Formula.  In the World:

=CALCULATE([Total Sales],
ALL(Periods),
FILTER(ALL(Periods),
Periods[Year]=MAX(Periods[Year])-1),
VALUES(Periods[MerchPeriod])
)

And its results:

No, it’s not perfect yet.  We will need to calculate percentage growth of course, rather than merely fetching last year’s sales.  And that given that 2011 is not yet complete, I’m a little squeamish about Last Year Sales returning ALL of 2010’s sales (the \$5.9M number).

But for now, let’s count our blessings.  This formula DOES fetch the sales from last year, and it works (mostly) both at the year level and the MerchPeriod (aka Month) level.

### Dissecting the Formula

Here is the formula again:

=CALCULATE([Total Sales],
ALL(Periods),
FILTER(ALL(Periods),
Periods[Year]=MAX(Periods[Year])-1),
VALUES(Periods[MerchPeriod])
)

But really, the GFITW is not a formula.  It is a pattern that you can re-use and modify.  So let’s look at it that way:

=CALCULATE(Original Measure,

Year Column Or Similar =

Expression that “moves” the Year Column

),

VALUES(
Time Column That is More Granular
Than Year, Like Month or MerchPeriod

)

)

Where everything in blue is “fixed” as part of the pattern, and everything in italics is something you can change.

Like I said, this is running a bit long for one post.  Come back Thursday for the rest

#### Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

## This Post Has 6 Comments

1. sherifffruitfly says:

i’m not getting something.

i have a table showing sales on a weekly basis for a variety of stores. i’m trying to add a column to the table, PreviousWeekSales, for the purpose of week over week comparisons by store.

in the sales table, there are Store, WeekNumber (1, 2, 3, etc.), and SalesAmount columns.

there are no other date or time columns involved.

per your post, i created a separate WeeksTable, for all my WeekNumbers, and included a DayNumber column (1-7, repeating over each WeekNumber), since your formula-template indicated i need a finer grain than the weeks i’m actually interested in.

[PrevWeekSales]
= CALCULATE([m_SalesAmount],
ALL(WeeksTable),
FILTER(ALL(WeeksTable), WeeksTable[WeekNumber] = MAX(WeeksTable[WeekNumber]) – 1),
VALUES(WeeksTable[DayOfWeek])
)

looking at the data by store-week, it simply shows the CURRENT store-week SalesAmount.

What am I missing here?

2. are you creating this as a column in the table or a measure / calculated field? For this to work you have to create this DAX formula as a calculated field

1. sherifffruitfly says:

i initially tried it as a column, but i get the same result (current week is actually displayed) when i do it as a measure.

-sff