Time intelligence solved with modelling and crossfilter instead of DAX measures

A while ago I came across this interesting time intelligence solution that doesn’t use DAX measures to do time intelligence but rather solves it through the model. The pattern was created by the great Greg Galloway from Artis Consulting and I am blogging this with his permission.

This solution allows users to select time ranges from the model to be applied to any measure (and not build it in for every measure),  for example YTD, last week, last 14 days, MTD and so on like this:

Or you can select the calculation though a slicer:

even select multiple calculations and showing them side by side.

The model

This gives immense flexibility. So how does this work? The trick here is to persist the time ranges in the model and use crossfiltering to show the right time range. It starts out with a relatively straightforward model with a fact table and a date table, the date table only contains a date column.

I have renamed the date table to “hiddendate” and hid it from the model. The two other tables is were things really get interesting. 

As you can see we are adding another date table (DimDate) and a calculation table that both have cross filtering turned on. The DimDate table is what is exposed to the end users and is just a regular date table, no additions. The Date calculation table allows them to choose the calculation they to use.

Before we look at how it works let’s start with how to create the calculation table. To simplify matters we will start by adding two types of date calculations: The “current selection” and “Last week”.  To make this work we have to generate a date range for each “calculation”. We do this using a  calculated table.

Date Calculation =
UNION (
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( ALL ( ‘DateHelper'[Date] ), ALL ( ‘DateHelper'[Date2] ) ),
            “Date Calculation”, “Current period”,
            “Date sort”, 1
        ),
        ‘DateHelper'[Date] = ‘DateHelper'[Date2]
    ),
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( ALL ( ‘DateHelper'[Date] ), ALL ( ‘DateHelper'[Date2] ) ),
            “Date Calculation”, “Last Week”,
            “Date sort”, 2
        ),
        ‘DateHelper'[Date] >= ‘DateHelper'[Date2]
            && DATEDIFF ( DateHelper[Date2], DateHelper[Date], WEEK ) < 1
    )
)

This calculated table references another table that we haven’t talked about before called DateHelper. This table is nothing more then 2 columns that both have the same date.

Both dates are used independently to doa crossjoin on and get the daterange we need for each calculation. For the current date it returns the same date for the date2 column. To calculate last week we use a formula that gets all dates where the week number is the same for each date in the date table. It returns a list of dates that are between the current date and 7 days ago. In the table it looks like this:

We add 2 more column to the table, the “date sort” column allows us to sort the calculation when using it in slicers and such by setting sort by other column.

Finally the generated date range and the relationships in the model allow us to aggregate the measure with the sales for last week. The picture below shows the Date columns from the calculation table with the sales from the fact table as you can see for a single date we have the full range of date2’s and it’s corresponding sales.

When we take the DateKey from the DimDate table instead we get a single date with the aggregated sales of the last week:

Relationships

This is like magic :).  This all works thanks to the relationships let’s investigate how the filters work here. In the report we select a date to reference upon. You would set that with a filter on the report or over a particular time range. 

Once you set the date filter on DimDate it will filter the calculation table down to all the dates generated per “calculation” for the selected date by using the relationship between DimDate and Date Calculation. When we filter by the “Last week” calculation the Date Calculation table now is filtered by both Date from the DimDate table and the Date Calulation value. This results in a list of dates in the range determined by the calculation in the Date2 column. The Date2 column is related to the DateKey in the HiddenDate table and crossfiltering will make sure that the HiddenDate table is filtered to return all the dates in the range. Finally this will now filter the FactOnlineSales table to summarize all the values that are left after the filters are applied. 

This model makes it look like there is a single value attached to each date but in truth it is summarizing a list of values based on the range you need for your calculation.

Now that we understand how it works we can extend the Date Calculation table to include more calculations. 

Date Calculation =
UNION (
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( ALL ( ‘DateHelper'[Date] ), ALL ( ‘DateHelper'[Date2] ) ),
            “Date Calculation”, “Current period”,
            “Date sort”, 1
        ),
        ‘DateHelper'[Date] = ‘DateHelper'[Date2]
    ),
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( ALL ( ‘DateHelper'[Date] ), ALL ( ‘DateHelper'[Date2] ) ),
            “Date Calculation”, “L14D”,
            “Date sort”, 2
        ),
        ‘DateHelper'[Date] >= ‘DateHelper'[Date2]
            && DATEDIFF ( DateHelper[Date2], DateHelper[Date], DAY ) < 14
    ),
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( ALL ( ‘DateHelper'[Date] ), ALL ( ‘DateHelper'[Date2] ) ),
            “Date Calculation”, “LW”,
            “Date sort”, 3
        ),
        ‘DateHelper'[Date] >= ‘DateHelper'[Date2]
            && DATEDIFF ( DateHelper[Date2], DateHelper[Date], WEEK ) < 1
    ),
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( ALL ( ‘DateHelper'[Date] ), ALL ( ‘DateHelper'[Date2] ) ),
            “Date Calculation”, “LW YA”,
            “Date sort”, 4
        ),
        ‘DateHelper'[Date] >= ‘DateHelper'[Date2]
            && DATEDIFF ( DateHelper[Date2], DateHelper[Date], DAY ) > 365
            && DATEDIFF ( DateHelper[Date2], DateHelper[Date], DAY ) < 372
    ),
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( ALL ( ‘DateHelper'[Date] ), ALL ( ‘DateHelper'[Date2] ) ),
            “Date Calculation”, “YTD”,
            “Date sort”, 5
        ),
        ‘DateHelper'[Date] >= ‘DateHelper'[Date2]
            && YEAR ( ‘DateHelper'[Date] ) = YEAR ( ‘DateHelper'[Date2] )
    ),
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( ALL ( ‘DateHelper'[Date] ), ALL ( ‘DateHelper'[Date2] ) ),
            “Date Calculation”, “MTD”,
            “Date sort”, 6
        ),
        ‘DateHelper'[Date] >= ‘DateHelper'[Date2]
            && MONTH ( ‘DateHelper'[Date] ) = MONTH ( ‘DateHelper'[Date2] )
            && YEAR ( ‘DateHelper'[Date] ) = YEAR ( ‘DateHelper'[Date2] )
    )
)

This approach gives you  much flexibility, this is needed as often calculations are different per company. As this is DAX it will allow you to easily change the calculation and extend it to create rolling aggregates over sliding periods (e.g. last N weeks this year, last year) or any other time range metrics.

It also offers a great flexibility into the reports themselves and allows you to build something like this:

Or this:

Finally, caveats

This is a great trick but there are some caveats to it:
1) There is a limit to what you can do with this calculation, it doesn’t work very well for Year over Year Growth % date calcs so you will have to use measures regardless

2) You could have a very large date calculation table, for the calculations I used in the example the table has grown to 570,000 rows. Therefore the table grows fast if you add things like YTD. 

3) Finally: performance. As you have heard time and time again from us and MVP’s. Do not use Crossfiltering or performance will suffer. This example might be an exception depending on your scenario. In general you are replacing on the fly time calculations with persisted data that leverages relationships. Based on our experience the performance difference tends to be better if the you can leverage the model directly. This is something that you should test out for your scenario and model, it is hard to give a general recommendation.

You can download the example here. Let me know if you have used this approach to solve some interesting problem.