Getting Serious with Date Intelligence

Date intelligence refers to a broad set of data modeling patterns and functions that enable analysis across time periods. In this chapter and throughout this book, we use date intelligence to refer to this broad set of data modeling patterns and to distinguish the concept from DAX time intelligence functions. This is done for two reasons. First, to be more technically precise, date intelligence deals with dates (days, months, years), not time (hours, minutes, seconds). Second, DAX time intelligence functions are only useful if dealing with a standard (Gregorian) calendar, but many businesses use a financial calendar that is different than a standard calendar year. Date intelligence, the broad concept, is applicable to both the standard (Gregorian) calendar as well as custom financial calendars.

Fundamental measures, such as reseller net sales, as created in Chapter 3, Building a Power BI Data Model, are supplemented with date intelligence...

Technical Requirements

The following are required to complete the recipes in this chapter:

• Power BI Desktop
• Tabular Editor
• SQL Server with the AdventureWorksDW2019 database installed. This database and instructions for installing it are available here: http://bit.ly/2OVQfG7
• Files for this chapter can be downloaded from the following GitHub repository: https://github.com/PacktPublishing/Microsoft-Power-BI-Cookbook-Second-Edition

Building a Date Dimension Table

A date dimension is needed by almost all data models and particularly those that require date intelligence calculations (e.g. Year-to-Date, Year-over-Year) and company-specific calendars. Building a robust date table in the source system provides significant long-term value across BI projects and tools. A complete date table accounts for all the required grains or hierarchy levels of both the standard (Gregorian) calendar and any fiscal calendar specific to the organization. Additionally, surrogate key columns aligned to each grain are included to drive the sort order of report attributes and to enable date intelligence expressions.

This recipe includes a design phase to identify the required date dimension columns and a process for adding a date intelligence surrogate key column to a dimension.

Getting ready

Full date tables are available from various sources:

• The Kimball Group downloadable Excel file: http://bit.ly/2rOchxt
...

Preparing the Date Dimension via the Query Editor

In some BI environments, it is not feasible to alter the source date table as per the previous recipe or even modify the SQL view used to load the date dimension table. In these situations, Power BI Desktop's Query Editor and M expressions can serve as an effective alternative to deliver the same columns necessary to drive robust date intelligence analysis.

In this recipe, an example date dimension M query is shared that builds common date attributes as well as dynamic logical columns. Additionally, a process for adding sequential date intelligence columns via M expressions is also included.

Getting ready

To prepare for this recipe:

• Download CH6_DateTable.xlsx from the following GitHub repository: https://github.com/PacktPublishing/Microsoft-Power-BI-Cookbook-Second-Edition

How to Prepare the Date Dimension

To implement this recipe, perform the following steps:

1. Create a new Power...

Authoring Date Intelligence Measures

With a complete date dimension table in place, date intelligence measures can be developed to support common requirements, such as year-to-date, year-over-year, and rolling history, as well as more complex, context-specific behaviors.

The date intelligence patterns described in this recipe are applicable to both standard and non-standard financial calendars, as they leverage fundamental DAX functions and the sequential date intelligence columns created earlier in this chapter.

This recipe includes examples of core year-to-date and prior year measures, as well as a more advanced dynamic prior period measure that adjusts to all grains of the date dimension.

Getting ready

To prepare for this recipe, follow these steps:

1. Complete the Preparing the Date Dimension via the Query Editor recipe in this chapter, or have a date table with the same columns as in that recipe.
2. Edit the initial part of the DatesInterim query to...

Developing Advanced Date Intelligence Measures

Date intelligence measures are often at the center of the most visible Power BI reports and dashboards, as well as more complex business analyses. Therefore, given the unique requirements of each organization and BI project, it is important to understand how to go beyond the standard patterns described in the previous recipe to efficiently embed custom logic. Additionally, the ability to answer the business question "When did X occur (or not occur)?" is a powerful supplement to data models that can be supported via DAX measure logic.

In this recipe, an example is provided of a measure that identifies the dates in which sales were not recorded for a specific region and product category. In addition, a custom prior year-to-date measure is described with default (no filter) behavior and the exclusion of incomplete periods from the current year.

Getting ready

To prepare for this recipe, follow these steps:

...

Simplifying Date Intelligence with DAX and Calculated Tables

In addition to the M query transformations described earlier in this chapter, DAX table functions can also be used in Power BI Import mode models to enhance and simplify date intelligence. DAX queries can access existing tables in the data model, and the tables evaluated during refresh can be used in relationships and measure calculations like all other tables. Similar to calculated columns, calculated tables should be rarely used—given the transformation capabilities of M, SQL, and ETL tools—but can be valuable supplements to models for small tables, such as role-playing date dimensions and bridge tables.

This recipe provides an example of using DAX calculated tables to support role-playing date dimensions. Additionally, a single row table is created via DAX to simplify common date intelligence measures.

Getting ready

To prepare for this recipe, follow these steps:

1. Open Power BI Desktop...

Leveraging Calculation Groups

When working with date intelligence, it is common to have many quasi-redundant measures that calculate various different metrics for YTD, MTD, PY, PYTD, PMTD, and other common date intelligence intervals. For example, business users may be interested in a year-to-date, year-over-year, and year-over-year percentage calculation for the gross sales, net sales, and other measures. Normally, this would require nine DAX measures to calculate these measures at each of those date intelligence intervals.

Calculation groups can greatly assist in reducing the number of measures required by allowing a DAX expression to be reused for any measure. In the example given, this means that three DAX expressions could serve the needs of the nine DAX expressions required without the use of calculation groups.

This recipe demonstrates the use of calculation groups in the context of date intelligence in order to eliminate redundant DAX expressions.

Getting ready...

Conclusion

This chapter provided recipes for preparing a data model to support robust date intelligence and for authoring custom date intelligence measures. Date intelligence provides a broad set of data modeling patterns and functions that enable analysis across time periods. The measures created for date intelligence can be used in combination with one another to support growth and variance measures and are often utilized as KPIs in Power BI dashboards. When properly implemented, date intelligence dramatically expands the analytical power of a data model and simplifies report and dashboard development.