Point in time analysis perfected

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.

Below a snippet of the separate period table I imported. It can be of any source in this case it is a copy of the data dimension table in our data warehouse.

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:

=CALCULATE(SUM(Assignment[Planned hours]);

FILTER(Assignment;

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?

Advertisements

6 thoughts on “Point in time analysis perfected

  1. Very interesting post – thank you for sharing!

    What I wonder is how I could handle the slightly different scenario where I want to be able to slice the sales transactions on the different values?

    Adapting your formula looks like

    =CALCULATE(SUM(‘Sales Invoice Line'[Amount Including VAT]);

    FILTER(‘Sales Invoice Line’;

    Related(‘Sales Invoice Header'[Posting Date]) = MAXX(VALUES(BasicCalendar); BasicCalendar[Datekey])

    ))

    Unfortunately this does not work in my situation since for example filtering only to the month of January 2012 brings the same result as filtering on January 31rd (what makes sense due to the way the MAXX-function works). Do you have any idea how I could change the formula?

    Thanks and regards,
    Julian

  2. I do have a transaction-table with a posting date that I want to slice on the different levels.

    Transaction-table:
    Entry No | Posting Date | Amount etc.
    1 | 01/01/2012 | 100
    2 | 02/01/2012 | 150

    If I use my formula from above and filter to the entire January (i.e. no filter on the day column) it returns the value of January 31rd (because maxx yields the last day of that period).

    I hope this explains it a little bit better.

    • What do you want to see when u slice on month january? 250 (sum of 100 and 150)? or the last amount known ofr that period? 150.

      If you want to see 150 then this expression will work:

      =CALCULATE(SUM(Table1[Value]); FILTER(Table1; Table1[Date] = MAXX(VALUES(Table1[Date]); Table1[Date])))

      Table1 consisting of column Date and Value. I used a seperate date dimension to slice to month, year or whatever.

      • Thank your very much for your help!

        I actually want to see 250 if I slice to the entire January.

      • There are two approaches.

        Add two calculated columns, one for year “YEAR(Table1[Posting Date])” and one for month “MONTH(Table1[Posting Date])”. Use these to slice to year and month
        The other option, which I recommend, is adding a seperate “Date” dimension table and relate it to your transaction table on column Posting Date. Your “Date” dimension table consists of all dates within a certain amount of years and contains the month, year and other categories belonging to each specific date.

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