Microsoft Power BI Cookbook

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 ...