Dynamic date columns

  1. Identify the grain(s).
    • Dashboards are usually focused on the current year, month, or week, and trends within the current year or recent history
    • Whether calendar or fiscal periods, columns associating dates to these values enhances and simplifies analysis
      • Time Intelligence measures, reviewed in Chapter 6, Getting Serious with Date Intelligence, enable the comparison of distinct time frames
  2. Modify the date view.
    • Add columns to the SQL view supporting the date dimension table for the required grains with dynamic expressions
 CASE     WHEN YEAR(D.Date) = YEAR(CURRENT_TIMESTAMP) THEN 'Current Calendar Year'    WHEN YEAR(D.Date) = YEAR(CURRENT_TIMESTAMP)-1 THEN 'Prior Calendar Year' WHEN YEAR(D.Date) = YEAR(CURRENT_TIMESTAMP)-2 ...

Get Microsoft Power BI Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.