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!

Share This:
Twitter
Facebook
LinkedIn

Stay up to date with the latest customer data news, expert guidance, and resources.

Iteration and Error Handling in M Query

All iteration roads in M Query eventually lead to the List.Generate function. In strengthening our data manipulation skills through Power Query, we absolutely need this looping function.

More Resources

Trusted By Over 6 Million End Users​

Industry leaders and over 6 million end users choose SkyPoint as their comprehensive and compliant Modern Data Stack Platform. A Gold Certified Microsoft Partner, SkyPoint Cloud turns siloed customer data into connected customer experiences.