8 Easy Steps to Create a Calendar Table in Power BI

If you’re using Power BI to analyze data, one of the most used reference tables will be a Calendar Table, which helps to split your data by year, month, day, quarter, and even fiscal year. It makes reporting and building visuals much easier.

The great thing about Calendar Tables is that you can have a Power BI Calendar Table created in only a few minutes regardless of whether you’re a DAX expert or not.

Let me show you how, step by step.

Step 1: Open Power BI Desktop

First, open the Power BI Desktop app on your computer. This is where we’ll build the calendar table.

Step 2: Create a New Table

At the top, click on the Modeling tab. Then click on “New Table”.

This will open a formula bar where you can enter a DAX formula to create the table.

Step 3: Add the Calendar Formula

Let’s name the table D_Calendar (you can choose any name you like).

Now, type this DAX formula:

D_Calendar = CALENDAR(

    DATE(2015, 1, 1),

    DATE(2022, 12, 31)

)

This will create a list of all dates from January 1, 2015 to December 31, 2022.

Tip: Change the start & end dates to relate to whatever timeframe your data incorporates.

If you already have your own dates in your data, you can also use this instead:

CALENDARAUTO()

This will automatically find the date range based on your data.

Step 4: Add a Year Column

Now let’s add some extra columns to break down the date.

Click on “New Column”, and type this:

Year = FORMAT(D_Calendar[Date], “YYYY”)

This will add a column that shows the year for each date.

Step 5: Add a Month Name Column

Next, we’ll get the month name from the date.

Again, click “New Column”, and enter:

Month_Name = FORMAT(D_Calendar[Date], “MMM”)

This gives you short month names like Jan, Feb, etc. And if you want full month names like “January”? Just change “MMM” to “MMMM”.

Step 6: Add a Day Column

Let’s pull out the day number. Click “New Column” again, and type:

Day = FORMAT(D_Calendar[Date], “DD”)

This shows the day of the month (like 01, 02, up to 31).

If you want the day name instead (like Monday, Tuesday)? Use “DDDD” like this:

FORMAT(D_Calendar[Date], “DDDD”)

Step 7: Add a Fiscal Year Column

Some companies don’t start the year in January. If that’s you, this step helps!

Here’s a formula that gives you the fiscal year:

Fiscal Year = IF(

    MONTH(D_Calendar[Date]) >= 1,

    YEAR(D_Calendar[Date]),

    YEAR(D_Calendar[Date] – 1)

)

In this example, the fiscal year starts in January. If your fiscal year starts in a different month like April or July, you’ll need to change the logic to match that.

For a fiscal year starting in March, use: CALENDARAUTO(3)

Step 8: Add a Quarter Column

And here comes the Last step! Let’s create a column that shows which quarter each date belongs to.

Click “New Column” again, and type:

Quarter = “Q” & FORMAT(D_Calendar[Date], “Q”)

This will show values like Q1, Q2, Q3, and Q4.

And that’s it, you’ve created a complete Power BI calendar table!

Now you can use this table to:

• Group your data by year, month, day, or quarter
• Create custom time filters
• Build better charts and visuals
• Handle fiscal years if your business doesn’t follow the standard calendar year

Quick Overview:

This is what we did:

• Opened Power BI Desktop
• Created a new table with CALENDAR() or CALENDARAUTO() function
• Then we created additional columns such as Year, Month Name, Day, Fiscal Year and Quarter

Why a Calendar Table is Important

Without a proper calendar table, Power BI doesn’t always know how to group your data by time. You may get charts with missing months or data that doesn’t sort correctly. 

By creating your own Power BI calendar table, you stay in control. You get cleaner visuals, easier filtering, and smoother DAX formulas.

Final Tip

Want to save time in future projects? You can copy this calendar table into your next Power BI file. Just adjust the start and end dates or use CALENDARAUTO() and you’re good to go! You can even save it as a template file (.pbit) to reuse again and again. This way, you won’t have to rebuild the table from scratch every time.

Download our free Power BI calendar table tool for easier date management and modeling.