Trailing History Requirements

A common requirement for fact tables and BI queries generally is to retrieve a specific date interval relative to the current date. One simple example is the trailing three years defined as the dates from three years ago (e.g. November 2, 2014) through the current date. Another example is to retrieve three full calendar years of history plus the current year such as (January 1, 2014) through the current date.

SQL Options

There are well established techniques with T-SQL (e.g. DATEADD) to implement this logic and as described previously,  in many cases an M query can simply reference an existing SQL view. Additionally, if a SQL object can’t be created in the source database the Value.NativeQuery() M function can be used to pass a SQL statement that contains the necessary logic.

In a perfect world every dimension and fact table of a Power BI dataset would be fully represented by a source SQL view (and it’s supporting data warehouse and ETL/ELT architecture) thus leaving no additional transformations for M queries. But as we know, even with mature data warehouses this just isn’t the reality for many environments and some level of M query transformation is needed to supplement or enhance the existing sources.

Inline Trailing Period M Queries

If we only need to apply the filter logic for one query or if the dataset is more of a quick proof-of-concept then it might be sufficient to create the M variables inline as follows:

Since we’re only looking at the filter condition for this blog post I’m just using a date table as the source and selecting only the Date column. Given the Date variables and the filter expression the dates from 11/2/2014 through the current date are returned:

The View Native Query dialog reveals the SQL query generated by the M expression:

A similar inline approach can be used to return three full calendar years (January 1, 2014) plus the current year:

The StartDate variable constructs a date value via #date and all dates from January 1, 2014 through the current date are returned:

See the other Date functions such as Date.AddDays(), Date.AddWeeks(), and Date.AddMonths() to apply similar filters for these time periods.

Staging Queries Only

If the same filter logic is going to be applied to several queries in the dataset or if you just want to eliminate any inline variables for manageability reasons then you could use staging queries, query parameters, or both. The values returned by these queries are not loaded to the data model (gray font) but are refreshed and can be referenced by other queries.

The CurrentDateQry and ThreeYearsAgoQry (defined as Date.AddYears(CurrentDateQry,-3) staging queries can then be directly referenced in the filter condition thereby eliminating inline variables per the following example:

The same query folding occurs to produce a SQL statement with a WHERE clause greater than or equal to November 2nd, 2014 and less than or equal to the current date (November 2nd, 2017).

To retrieve the three full years (January 1, 2014 and later) plus the current year, only one date variable is needed and this leverages the staging query (ThreeYearsAgoQry):

Parameterized Staging Queries

For a greater level of abstraction and manageability you might replace the staging queries with generic “starting date” and “starting year” queries:

Both staging queries reference the TrailingYears query parameter currently set to a value of three. With parameterized staging queries, a single  change to the parameter can flow through to the staging queries and then the queries that are loaded to the data model.

Parameterized Inline Variables

As yet another option, you could use the parameter to simplify an inline date variable such as StartDate in the following example:

Summary

All the approaches described in this post result in SQL queries for the source system to execute. Which of the four approaches (inline variables only, staging queries only, parameterized staging queries, parameterized inline variables) is the ‘right approach’ depends on the scenario. In general, larger datasets and higher levels of re-usability will suggest additional layers of staging and parameters.

We’ll briefly review these queries at Boston Code Camp 28 and related M query filter patterns and examples will also be included Mastering Power BI.