Date intelligence metrics

Date intelligence metrics are typically the first set of measures to be added to a dataset following base measures. These measures reference the base measures and add a custom filtering condition to the Date dimension table, thus providing visibility to multiple distinct time intervals, such as year-to-date and the previous year-to-date. Given their built-in date filtering logic, Power BI reports and dashboards can be developed faster and without manual maintenance costs of updating date filter conditions.

The following four measures apply custom filter contexts to either return the current year, month, and week by default, or the latest of these time intervals given the date filters applied in a report:

Internet Net Sales (CY) = CALCULATE([Internet Net Sales],FILTER(ALL('Date'),
  'Date'[Calendar Year] = MAX('Date'[Calendar Year]) && 
  'Date'[Date] >= MIN('Date'[Date]) && 'Date'[Date] <= MAX('Date'[Date])))

Internet Net Sales (YTD) = CALCULATE([Internet Net Sales],
  FILTER(ALL('Date'),'Date'[Calendar Year] = MAX('Date'[Calendar Year]) && 
    'Date'[Date] <= MAX('Date'[Date])))

Internet Net Sales (MTD) = CALCULATE([Internet Net Sales],
  FILTER(ALL('Date'),'Date'[Calendar Year Month Number] = MAX('Date'[Calendar Year Month Number]) && 
    'Date'[Date] <= MAX('Date'[Date])))

Internet Net Sales (WTD) = CALCULATE([Internet Net Sales],
  FILTER(ALL('Date'),'Date'[Calendar Year Week Number] = MAX('Date'[Calendar Year Week Number]) && 
    'Date'[Date] <= MAX('Date'[Date])))

The use of the MIN() and MAX() functions within the FILTER() function invokes the filter context of the report query. For example, if a report page is filtered to the second quarter of 2016 (2016-Q2), the CY measure will only return the sales from these three months while the YTD measure will include both the first and second quarter of 2016. The month-to-date (MTD) and week-to-date (WTD) measures will return the sales for June of 2016 and Week 27 of 2016, the last month and week in the filter context.

The date dimension table only contains rows through the current date. Therefore, in the absence of any other date filters applied in a report, these measures default to the current YTD, MTD, and WTD totals for net sales per the following multi-row card visual:

Date intelligence metrics for the current year

The (CY) measure returns the same value as the YTD measure when no other date filters are applied.

The MTD and WTD measure both references a numeric column on the date table that corresponds to the given granularity. For example, December of 2016 and January of 2017 are represented by the values 96 and 97 in the Calendar Year Month Number column. As shown in the previous chapter, these sequential columns are critical for date intelligence and are also used by the Sort By column property.

The following set of DAX measures return the prior year, month, and week given the filter context of the report:

Internet Net Sales (PY) = CALCULATE([Internet Net Sales],FILTER(ALL('Date'),
  CONTAINS(VALUES('Date'[Prior Calendar Year Date]),'Date'[Prior Calendar Year Date],'Date'[Date])))

Internet Net Sales (PYTD) = CALCULATE([Internet Net Sales],
  FILTER(ALL('Date'),'Date'[Calendar Year] = MAX('Date'[Calendar Year])-1 && 
    'Date'[Date] <= MAX('Date'[Prior Calendar Year Date])))

Internet Net Sales (PMTD) = CALCULATE([Internet Net Sales],
  FILTER(ALL('Date'),'Date'[Calendar Year Month Number] = MAX('Date'[Calendar Year Month Number])-1 && 
      'Date'[Date] <= MAX('Date'[Prior Calendar Month Date])))

Internet Net Sales (PWTD) = CALCULATE([Internet Net Sales],
  FILTER(ALL('Date'),'Date'[Calendar Year Week Number] = MAX('Date'[Calendar Year Week Number])-1 && 
      'Date'[Date] <= MAX('Date'[Prior Calendar Week Date])))

The Calendar Year, Calendar Year Month Number, and Calendar Year Week Number columns used by the current period measures are also referenced by the prior period measures. However, the prior period measures subtract a value of one from the result of the MAX() function to navigate to the given preceding period.

In the PY measure, the CONTAINS() function used within the filtering parameter of the FILTER() function returns a true or false value for each prior calendar year date based on the date column. The date column reflects the filter context of the report query and thus only the corresponding prior year dates are passed to FILTER() as the modified filter context.

DAX provides a number of functions dedicated to date intelligence, such as DATEADD() and SAMEPERIODLASTYEAR(). These functions are much less verbose than the techniques from these examples, but they're also generally limited to standard calendars. The approach described in this section leveraging core DAX functions, such as FILTER() and ALL(), can also be applied to financial calendars. Additionally, the filter navigation (for example, MAX() - 1) implemented in the prior period measures is applicable to more advanced date intelligence expressions.

Each prior period measure references a column containing date values that have been adjusted relative to the date column. The following screenshot of the date dimension table in SQL Server highlights these three columns relative to the date column:

Prior date columns in the date dimension

Given the value of date intelligence measures and the relative static nature of the date dimension, it's recommended to develop a robust date dimension table. If the necessary columns cannot be implemented in the source database itself, the columns can be computed within the SQL view or the M query of the Date table.

Table of Contents for Date intelligence metrics

Create new playlist

Sign In

Sign Up

Table of Contents for
Date intelligence metrics