Relative Date Dimension

Hi Mariusz - 

Thanks for the article. I highly recommend everyone include relative dates in their date dimensions. This seems to be a good place to discuss related ideas. 

1. Name the relative dates. One benefit of this is to create a slicer with "Current Year" selected. The user can easily switch to "Last Year" if desired. Something like this:

    InsertRelativeYearDescription = Table.AddColumn(InsertRelativeYear, "Relative Year Description", 
                                    each if [Relative Year] = 0 then "Current Year"
                                        else if [Relative Year] = -1 then "Last Year"
                                        else if [Relative Year] = 1 then "Next Year"
                                        else if [Relative Year] < -1 then Number.ToText(Number.Abs([Relative Year])) & " Years Back" 
                                        else Number.ToText([Relative Year]) & " Years Ahead")

2. Similarly, we can Group relative dates. The following is a simple version, but customized groupings could also be created to reflect business requirements, such as grouping 5 week periods compared to today's date for comparison purposes.

    InsertYearGroup = Table.AddColumn(InsertRelativeDayDescription, "Year Group", 
                                    each if [Relative Year] = 0 then "Current Year"
                                        else if [Relative Year] < 0 then "Past Years"
                                        else "Future Years")

3. This article refers to relative dates in particular, but it's worth mentioning some other potential enhancements in a date table: Fiscal Periods, workdays and whether a day is before or after a particular event (such as "Are the financial books closed for this period?"). 

Thanks again Mariusz!

Nathan