Creating a Really Simple Date Table in Power BI

Leave a comment

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.

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.comLinkedIn Twitter | YouTube

 

Leave a Reply

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