# Point-In-Time Dimension Reporting In DAX

Before I start, I have to state that the technique shown in this post isn’t mine but was developed by my colleague Andrew Simmans, who has very kindly allowed me to blog about it.

Over the last few months I’ve been working on an SSAS Tabular project that has not only presented some interesting modelling challenges, but has shown how DAX can offer some new and interesting solutions to these challenges. Consider the following scenario: a supermarket sells products, and we have a fact table showing sales of products by day. Here’s some sample data:

To complicate matters, each product has one product manager but product managers for particular products change from time to time. Normally this might be solved by adding the product manager name to the Product dimension table and implementing a Type 2 Slowly Changing Dimension. In this case, though, we want something slightly different: instead of seeing sales attributed to the product manager who was in charge of the product at the time of the sale, and therefore seeing sales for the same product attributed to different product managers on different dates, we want to attribute all sales for a product to a single product manager but be able to use a second date dimension to be able to determine the point in time, and therefore the product manager in charge of each product at that point in time, that we want to report as of. To put it another way, we want to be able to find the state of a dimension on any given date and use that version of the dimension to do our analysis.

For example, we have the following table showing which product manager was in charge of each product at any given point in time:

Between January 1st 2013 and January 3rd 2013 Jim was the product manager for Orange, but from January 4th 2013 onwards Rob took over as product manager for Oranges; Fred was the product manager for Apples the whole time. We want a PivotTable that looks like this when we choose to report as of January 2nd 2013:

Notice how Jim is shown as the product manager for Oranges. If we wanted to report using the managers as of January 5th 2013, we would want to see Rob shown as the product manager for Oranges like so:

The solution to this problem involves a combination of two DAX techniques that have already been blogged about quite extensively and which I’d encourage you to read up on:

• Many-to-many relationships, in this case the solution developed by Gerhard Brueckl, described on his blog here.
• ‘Between’ date filters, which I wrote about recently but which Alberto has recently improved on in his must-read white paper here.

Here are the table relationships I’ve used for the sample scenario:

I’ve added a second date table called ReportingDate which contains the same rows as the Date table shown above; note that it has no relationship with any other table.

This problem is very similar to a many-to-many relationship in that a product can have many managers across time, and a manager can have many products. Indeed we could model this as a classic many-to-many relationship by creating a bridge table with one row for each valid combination of product and manager for each possible reporting date; on my project, however, this was not a viable solution because it would have resulted in a bridge table with billions of rows in it. Therefore, instead of joining the ReportingDate table directly to the ProductManager table, we can instead filter ProductManager using the between date filter technique.

Here’s the DAX of the Sum of Sales measure used in the PivotTables show above:

`Sum of Sales:=`

`IF(`

`HASONEVALUE(ReportingDate[ReportingDate]),`

`CALCULATE(`

`SUM(Sales[Sales]), `

`FILTER(ProductManager, MIN(ReportingDate[ReportingDate])>=ProductManager[StartDate] `

`&& `

`IF(ISBLANK(ProductManager[EndDate]), TRUE(), `

`MIN(ReportingDate[ReportingDate])<=ProductManager[EndDate])`

`))`

`, BLANK()`

`)`

This is not necessarily the best way to write the code from a performance point of view but it’s the most readable – if you need better performance I recommend you read Alberto’s white paper. What I’m doing is this:

• Only return a value if a single reporting date is selected
• Filter the ProductManager table so only the rows where the selected reporting date is between the start date and the end date are returned, ie we only get the rows where a manager was in charge of a product on the reporting date
• Use the filtered ProductManager table to filter the main fact table using the Calculate() function, in exactly the same way that you would with a many-to-many relationship

### 12 responses

1. John says:

Do you have a sample workbook that can be opened in Excel 2010? When I tried to do anything with the PivotTable or open the PowerPivot window I was told that the PivotTable/Data Model was created in a newer version of Excel and can’t be updated in this version of Excel.

• I should be able to create one for you. Let me get back to you…

2. we have a POC to implement a cube with sales amounts as a fact with different dimensions such as customer age, and the sales person Id. then the supervisor or a manager should query this cube through a performance point report and he should see only the sales amounts related to the sales employees under his umbrella, any directions please how to address this in designing and implementing the cube and the report?

• Sounds like dimension security should do what you need. If you have a lot of managers then you may need dynamic dimension security.

3. Robin says:

Hi
Jg would be grateful if I could get some help. I want to be able to produce the number of active rows per month / year over a period of time. I have start dates and end dates. in some cases there is no end date, it is empty.

Thanks
Robin

4. Amandeep Singh says:

This is very relevant for my user case.Any tips for improving the performance of DAX query above or for re-writing the query ? Thanks!

5. Brian Wang says:

Excellent blog, Chris. Appreciate it. I am also wondering if this model is scalable when there’s one more dimension joining. Let’s say, manager will have address as scd2. It may sound a little crazy in this case. What would you suggest to do the same for potentially 3 dimensions (product, manager, address (3 dimensions having scd2 mapped?

This site uses Akismet to reduce spam. Learn how your comment data is processed.