Date tables are one of my favorite things in Power BI, right up there with field parameters. Particularly the ability to make all of my date filtering relative is one of the things I really love about them – it can really reduce the maintenance for reports to have measures and filters “stick” to current or last fiscal year for example vs hard-coding fiscal year numbers into reports.
In this video about how to use date tables in Power BI, we go through how to make a “sticky” customized relative date slicer. Here’s the custom column formula we used (I’m posting it here because YouTube doesn’t like some of the special characters in the video descriptions).
if [Fis Year Relative Num] = 0 then "Current Year" else
if [Fis Year Relative Num] = 1 then "Next Year" else
if [Fis Year Relative Num] = -1 then "Last Year" else
if [Fis Year Relative Num] < -1 then Number.ToText([Fis Year Relative Num]*-1) & " Years Back" else
if [Fis Year Relative Num] > 1 then Number.ToText([Fis Year Relative Num]) & " Years Ahead" else nullThis field can go into a slicer or a filter pane to let a user select a relative fiscal year. You can use other relative number columns to do the exact same thing for fiscal quarters, weeks, months, so on so forth.
Here’s the end result:
