# 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 🙂

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?

## 6 thoughts on “Point in time analysis perfected”

1. julian says:

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?

=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

• I am not sure i understand your problem. Can you give an example?

2. julian says:

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.

• julian says: