Home › Power BI › Creating a Really Simple Date Table in Power BI
Power BI · Jul 26, 2018

Creating a Really Simple Date Table in Power BI

Date Tables in Power BI are used to display dates in reports, filter by dates in reports, and use time intelligence functions in Power BI.

Date Tables can be created in DAX or Power Query M Language. To create a really simple date table using DAX, you can use CALENDAR or CALENDARAUTO functions. Both these functions will create a table with one field, a Date field.

For example, go to Modeling in Power BI Desktop and select New Table:

Enter the formula below. We will create a table with dates from 1/1/2017 to 12/31/2019:

Dates = CALENDAR(DATE(2017, 01, 01), DATE(2019, 12, 31))

Now, as mentioned there is only one field in the table. We can add more columns to build out this table.

Let’s add a Year column. Select New Column:

Add the formula:
Year = YEAR(Dates[Date])

Now the Month and Day:

Month = Month(Dates[Date])

Day = Day(Dates[Date])

You can see the date table is now forming. We will want to add fields such as the Month Name:
Month Name = FORMAT(Dates[Date], “mmmm”)

And other fields to help users use dates in their reports.

The final step is to mark the table as a Date Table, to help Power BI with time intelligence.

Leave a Reply

Your email address will not be published. Required fields are marked *

Carl de Souza
KEEP LEARNING. KEEP BUILDING.

Explore AI, agents & Microsoft technology.

I share practical ideas, tutorials, and videos about AI, AI agents, Microsoft technologies, and the Power Platform.