Traveling into the Future: Measuring Things that Lag

 
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.

 

I get the sales data from multiple sources, but the “richest” such source come from MrExcel himself, since he’s my publisher (Holy Macro Books – just an awesome name for a publishing company).

This is gonna shock you, but when I get the data from Bill, it comes in the form of…  an Excel workbook.  (I know, just SO out of character for him!)

Said workbook is produced and formatted, by Bill, via the use of VBA macros.  I know, the surprises just keep coming.

It looks like this:

image

This is how the data arrives.  Not the most convenient format.

Note how a single line can represent one sale through MrExcel.com, OR it can represent ALL sales reported via the ‘”real” book trade for an entire month.  This is based on the Customer column:

image

The three redacted customers each bought 1 copy from MrExcel.com.  The “IPG Cust” line represents sales reported by the “real” book trade (Amazon, Barnes and Noble, etc.)

Anyway, a simple CALCULATE takes care of that by testing for Customer = “IPG Cust” or Customer <> “IPG Cust”

But that’s not the point of this post.

Any Business That Pays You Four Months Later is an Interesting Business

Let’s say you buy a copy of the book from Amazon today.  I won’t receive my royalty payment (a fraction of the selling price) until February, because that’s how the book trade works.

If you buy a copy from MrExcel.com instead, I get my royalty (still a fraction, but double the fraction I get from the book trade) at the end of this month.  Because that’s how Bill Jelen works.

That makes analysis interesting because the dates don’t line up.

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

[Via Book Trade Lag] Shows the [Via Book Trade] Amount Four Months Earlier,
Which is When those Sales Actually Happened

And what’s the formula for [Via Book Trade Lag]?

[Via Book Trade Lag] =

CALCULATE([IPG Total All Formats],
           FILTER(ALL(Periods),
                  Periods[PeriodID]=MAX(Periods[PeriodID])+4
                 )
          )

A few things worth noting here:

  1. I’m using a custom calendar, so that means I can’t use DATEADD
  2. Technically this is the GFITW pattern, but note that it’s missing an ALL(Periods) outside of the FILTER?  Turns out that extra ALL() isn’t necessary in 99.9% (and probably 100%) of cases.  Which is a touch embarrassing for me and the topic of a future post.
  3. In every time-navigating formula I had ever written before this one, I was navigating back in time, so the arithmetic was minus something.  This time it’s plus.

Navigating forward in time!  I LOVE that!  Yes, it’s intuitively backwards, since “lag” makes us think “back.”  The way we have to write the formula though is to go “into the future” and grab its number to display at the date we’re currently “at” in the pivot.

And now I can view my book sales and see the actual sales trends:

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

Thanks to the “Lag” Measure, this Chart Tracks the Real World Sales of the Book

Kinda cool.  A big initial sales number when the book released, then the inevitable decline.  But then then the trend reversed!  (No doubt due to Power Pivot’s increasing popularity over time.)

Another usage:  ordering new inventory “just in time”

I built all of that book sales tracking stuff several months ago, but I did some work for a client yesterday that was similar, and it reminded me to post about this technique.

Short version of yesterday’s problem:  if we forecast that we are going to run out of inventory in eight weeks, but it takes six weeks to receive that new inventory after we order it, we better get that purchase order into the pipeline within the next two weeks.

And we basically “landed” on the same formula as above.  Cool huh?

We were also using a custom calendar yesterday, but if we’d been using a standard calendar, we could have used DATEADD(Calendar[Date], 4, Month) within our CALCULATE and achieved the same effect.

One Response to Traveling into the Future: Measuring Things that Lag

  1. Marco Russo says:

    Nice post Rob (and good sales, by the way!).
    I’m working on a more general pattern about custom calendar time intelligence these days and it is a very wide topic – I’d like to see more examples of “real needs” from the field, so I will focus on more useful examples. This is a very good one, thanks!

Leave a Comment or Question