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.
Show us the money
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).
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:
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.