Add a Calendar Table

April 19, 2023 - by Bill Jelen

As your Power BI skills advance, you may start writing DAX formulas that rely on time intelligence. These functions work better when you have a calendar table in the data model.

It is simple to create a calendar table. Insert a new worksheet in your workbook. Find the earliest and latest dates in your data. With a heading of Date in A1, put the earliest date in cell A2. Select A2 and drag the fill handle down to add dates in column A until you have enough dates to get you through the latest date. If you are building a dashboard in September and you expect to add more data for the rest of the year, it is fine to have the calendar table go through the end of the year.

Add columns such as Month, MonthNumber, Year, Quarter. Month Number might seem like a silly column to have, but you will have to teach Power BI that month names need to be sorted by month number, or your reports will end up sorted in alphabetical month sequence: Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar, May, Nov, Oct, Sep.

The formulas used in the following figure are =TEXT(A2,"MMM"), =MONTH(A2), =YEAR(A2), =CHOOSE(B2,"Q1","Q1","Q1", "Q2","Q2","Q2", "Q3","Q3","Q3", "Q4","Q4","Q4").

This article is an excerpt from Power Excel With MrExcel

Title photo by Towfiqu barbhuiya on Unsplash