Getting Relative Month/Year Columns Into a 445 Calendar Table

Relative Month and Year columns in calendar tables are indispensable. They let you create simple, easy to read time intelligence measures that don’t require recoding at the end of each year/month to redefine what “this year/month” is. With them you can write real simple measures like:

Total Sales Current Year = 
CALCULATE(
    [Total Sales],
    DimCalendar[Relative Year] = 0 //AKA This Year
)

or

Total Sales Last Month = 
CALCULATE(
    [Total Sales],
    DimCalendar[Relative Month] = -1 //AKA Last Month
)

But drop into the word of 4-4-5 calendar tables and they become much harder to create. In this video we’re going to show you an easy way to generate them using a self join.

Happy Reporting!