Solving the “Point in time” problem with PowerPivot

Several posts ago I wrote about, what I called, the “point in time” analysis problem. You can read that here. In short; Let’s say the business user wants to see what the most current planning was on January for February and then drilldown to what the current planning was in in a certain week within January. I call this scenario “Planned Planning”.

Most of the semi solutions I was able to come up with had one important draw – back. They forced the user to analyse planning from a fixed time perspective (date). In other words, the user was not able to jump from month level to, for instance, week level and see the current planned planning on those levels.

DAX to the rescue

First of, let me say that I am no MDX wonder. Allthough the title above may suggest that this problem can only be solved using DAX, it most likely has to do more with my mediocre understanding of MDX. The cool thing is that this is precisly the main goal of PowerPivot.

Thanks to Kasper de Jonge I was able to understand the fundamental approach I should use using DAX. You can read his blog entry here. I strongly suggest you read this before reading more.

Show us the money

Our scenario:

Assignment 1: Ian 8 hours planned for 3-Jun; valid from 1-jan-2011 until 1-feb-2011.
Assignment 1: Ian 6 hours planned for 3-Jun; valid from 2-feb-2011 until 15-feb-2011.
Assignment 1: Ian 7 hours planned for 3-jun; valid from 16-feb-2011 until infinity.

We  want the user to be able to filter on month january 2011 and get 8 hours for 3 -Jun. Selecting only year 2011 will return 7 hours. etc etc. The point is that the user can get the current planning for every date level (Year, Month, Day).

The formula:

Basicly we determine if the user has selected a month, year and (or not) a day. This is done using IF() statements.

Tip: It is a large formula but one you set up once and reuse.

The result when selecting January of 2011:

The catch

Selecting february and january in 2011 will make the formula act like you want to see the current planning for 2011. But who knows, with SQL Denali and PowerPivot V2 just around the corner new and exiting things will be possible.

Advertisements

3 thoughts on “Solving the “Point in time” problem with PowerPivot

  1. Pingback: Point in time analysis perfected « [BI]an Smith

  2. Please let me know if you’re looking for a article writer for your site. You have some really good posts and I feel I would be a good asset. If you ever want to take some of the load off, I’d love to write some
    articles for your blog in exchange for a link back to mine.
    Please blast me an e-mail if interested. Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s