, ,

Lookups based on date ranges

Another one from the Mr. XL Forum!

…and the word “forum” gives me an excuse for a blast from the past…

PowerPivot Court

“What you are about to witness is real. The participants are not actors.  They have agreed to dismiss their tech support cases and have their questions settled here, in our forum:
THE POWERPIVOT COURT

(Click here for theme music!)

The much-neglected calculated column

OK, neglected by me, not by everyone else.  At Pivotstream I have the benefit of a great SQL team.  Generally speaking, when I need a calculated column, I ask them to provide it.  The benefits of doing that are documented several places on this site, including hereand continued here.

But hey, not everyone has a database rapid-response team at their disposal Sad smile

So questions like this one come up a lot on the forums, reminding me of my blind spot:

You have two tables of data.  In one, you have a list of your clients and their effective discount rate during particular date ranges – marked by a start and an end date.  If the End date is blank, that discount rate is assumed to still be active.

image

Rates Table:  Discount Rate per Client,
Over Distinct Date Ranges (Start and End Effective Date)

Then there’s a second table, listing Clients and the days that you called each of them:

Multiple Clients, and Multiple Calls to Each Client in PowerPivot

Calls Table:  Multiple Clients, and Multiple Calls to Each Client

Desired Result

You want to add a calculated column to that Calls table which contains that client’s effective discount rate on the date of the call:

Calc Column in PowerPivot Looking Up a Value Based on Date Ranges in Another Table

We Want to Add the Highlighted Calc Column – What’s the Formula?

So… what’s the formula?

=CALCULATE(AVERAGE(Rates[Rate]),
   FILTER(Rates,
          Rates[Start]<=Calls[ContactDate] &&
          Rates[EffectiveEnd] >= Calls[ContactDate] &&
          Rates[Client]=Calls[Client]
   )
)

Hey, That’s a Lot Like a Measure!

Yeah, it IS a lot like a measure.  It uses CALCULATE, the wonder function.

But in this case, we’re just using CALCULATE to apply filters in our calculated column.

Works basically the same way, though.  The three clauses in the FILTER() function are all AND’d together using the && operator, so that only rows from the Rates table meeting the following three criteria are “kept” and then “fed” to the AVERAGE function:

  1. The Start date in the rates table must be prior to (or equal to) the Contact Date in the Calls table.
  2. The End date in the rates table must be after (or equal to) the Contact Date in the Calls table.
  3. The Client ID in the rates table must match the Client ID in the Calls table

Note for those who desire deeper understanding:  The biggest difference between this calc column formula and a similar formula we’d have to write in a measure is that in a measure, we’d have to use the VALUES() function wrapped around the right hand side of the criteria – VALUES(Calls[ContactDate]) rather than just Calls[ContactDate]).  And we’d have to “protect” those VALUES functions with an IF(COUNTROWS()) since you can’t use VALUES() as part of an “=” test, except when there is only one value returned, or maybe use MAX() instead of VALUES().  The point is that in a calc column, we DO have a current row in the table, so we don’t need the wrapper function like we do in a measure.

What’s that EffectiveEndDate column?

Yeah I sneaked that one in.  It’s another calc column I added to the Rates table:

image

You can see its formula in the formula bar in the image above.  I just wanted to put a non-blank value in whenever End was blank.  You could hard-code a date in there if you wanted, or use TODAY() and a much bigger number than 365.  I just figured that setting a date one year in the future from today is good.  Keep in mind though that TODAY() will only be re-evaluated when you refresh your data or force a re-calc.

Is There a Relationship Between Those Two Tables?

No, there is not.  Neat huh?  And really, there couldn’t be anyway – each Client ID appears more than once in each table, and PowerPivot doesn’t handle relationships on “many to many” like that.  The date columns are even less “relationship friendly” since they don’t match up at all, and are based on ranges in one table and single dates in the other.

Download the Workbook

I’m trying to do this more often, especially with the forum questions.

Download the workbook here

Read more on our blog

Get in touch with a P3 team member

  • Hidden
  • Hidden
  • This field is for validation purposes and should be left unchanged.

This field is for validation purposes and should be left unchanged.

Related Content

Exciting Improvements to Power BI’s Export to Excel

It is a standing joke in the analytics industry that “Export to

Read the Blog

The case of the disappearing Field Parameters: SOLVED

The Case: The new Field Parameters feature from Microsoft had been added

Read the Blog

Completing the Set Up: Field Parameters Using Tabular Editor

May 2022’s release of Power BI Desktop is the best releases we’ve

Read the Blog

Calculation Groups to the Rescue!

o set the stage, I need you to travel back in time

Read the Blog