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!

Brian Grant

Brian Grant

Brian Grant is a masterful Senior Analytics Consultant by day, and a Power BI legend by night. A passion for Power BI drove him to holistically master the tool from all three perspectives: M, DAX, and the visualization layer. Brian is a constant teacher who leads Power BI training sessions, while sharing his wealth of expertise through video and blogging resources.
SHARE THIS
Share on twitter
Share on facebook
Share on linkedin
Share on email
RELATED POSTS

Subscribe

Subscribe to CSG. We’ll supply your inbox with the latest blog posts, training videos, and upcoming events.

Ready to wrangle your data?