A while ago I wrote about how you accomplish this using DAX in PowerPivot. You can read about it here. It is useful but I always thought there were two major drawbacks to that approach; Code length and Usability.
The expression I used was huge! No I mean really huge. This is due the number IF statements I used, one for each possible combination of user selections in the filter.
So I decided to rewrite the expression with these goals in mind; shorten the code, and thus make it easier to understand, and making it less error prone. This post is all about sharing this new-found knowledge with you 🙂
The business scenario
An organization wants to have insight in how and if planning assignments change for their employees, the goal is to minimize last-minute changes and thereby creating a more manageable process. The organization decides to harness the power of their data warehouse which tracks assignment changes using the Kimball type 2 approach.
Our goal for the user is to be able to select any period in time and get the current / valid planning for that period without the use of complex logic (technical or functional), aka “point and click”.
Getting in the details
Below a snippet of our large assignment fact table in our data warehouse
In short: Ian Smith’s planning has changes two times for the same assignment. Each change is marked with a valid from and expiry dat marking the period in which that assignment line was valid. John Snow’s planning changed one time.
No relationship is created between the two tables in PowerPivot. The period table is purely used for the user to select a period for which he/she wants to see the valid planning.
On to the DAX
This is the measure I created:
Assignment[Valid from date] <= MAXX(VALUES(Period); Period[Date])
Assignment[Expiry date] >= MAXX(VALUES(Period); Period[Date])
The CALCULATE is there to be able to influence the FILTER context. I could also have used SUMX. The FILTER returns a table in which all assignments are filtered; each assignment-line returned has a “validfrom” date smaller or equal and a “expiry” date larger or equal to the max date of what the user selected in the table period. The VALUES function returns the distinct values of what the user selected, in combination with the function MAXX that is always one row. The result being that the FILTER returns 31-jan-2009 when the user only selects 2009 or 31-jan-2009 when the user selects 2009 and month january.
The point is the user is free to see valid planning at year level as well as on day level or any combination in between without the use of complex IF statements. Pretty nifty huh?