Power BI date table creation manually and dynamically?

Power BI date table is essential in almost every data analytics project. With the help of the date table in power bi, we can write complex dax formulas to solve business problems based on date & time. It is crucial, especially in financial reporting & dashboard development. In this post, I will discuss two methods to work with the Power BI date table.

Import date table from a data source

In this method, you need to prepare date table in the data source. This source can be based on excel, CSV, or any database management system like SQL server management studio. Once the data is being imported, the next step is to check every column’s data types in the table. Every column in the table that you have to use to calculate values based on date time should have correct data types.

Once you are sure about the data types, the next step is to mark this table as a date table. To apply this operation, you have to do the following steps.

1. Open Power BI desktop.
2. Import data into Power BI & check/set proper data types.
3. Select the imported date table
4. Go to Table tools and click on “Mark as date table.”
5. One dialog, you have to choose on the column that needs to use for the date.

Calendarauto function to create a Power BI date table

Calendarauto function returns a table with a single column named “Date.” Calendarauto function will only work if the underlying data model contains any date columns. It automatically grabs minimum and maximum dates and then generates a continuous set of date range values.

Syntax of Calendarauto function is as below

Date = Calendarauto()

We can control automatic date ranges with the help of filter function. 

Calendarauto function with Filter function in DAX

We know that both the calendarauto function and a filter function in the dax return table. We can control the Calendarauto function’s output by wrapping it inside the dax filter function, as you can see below.

Dates = Filter(Calendarauto(),Year(Date) > date(2010,7,1))

The first parameter of the filter function is a table. In the above expression, we are providing a table as Calendarauto function. After this, we have applied condition on the column Date that is coming from the table generated by the calendarauto function.

2010-07-01 is the minimum date values that we got from our data model manually. If we got new data in the data model where the minimum date is 2009, what will happen? It is not an excellent approach to set calendarauto function every time and filter function to find and set conditions on the minimum date from the data model. To handle this issue, we need to find some dynamic approach.

Calendarauto function with DAX Variables

To set the minimum date, we need to use the power of dax variables. DAX variables execute within the current filter context, and we can achieve great results.

For this, we have to get dynamically minimum date with the help of DAX variables, and we can use that variable inside the Filter function, as you can see below.

Dates =
VAR MinDate = MIN(Sales[OrderDate])
Return
FILTER(
CALENDARAUTO() ,
YEAR([Date]) >= YEAR(MinDate))

Now with the help of the dax variable, we have achieved to get the minimum date dynamically. We have got a one-column table that contains continuous dates. We need to add further columns like a year, month, day, quarter, etc. To achieve this, we need to further enhance our DAX formula with help Addcolumns dax function.

Calendarauto function with Addcolumns function

Addcolumns function returns all the columns of the provided table plus any additional column. In our case, we can create a year, month, and day column very quickly.

Syntax of addcolumns is 

ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…) 

Now we can write the final DAX formula to achieve complete Power BI date table that will have all the required columns.

Dates =
VAR MinDate = MIN(Sales[OrderDate])
VAR Customdate =
FILTER(
CALENDARAUTO() ,
YEAR([Date]) >= YEAR(MinDate))
RETURN
ADDCOLUMNS(Customdate,
"Year", YEAR([Date]),
"Month No", MONTH([Date]),
"Month name", FORMAT([Date],"mmm"),
"Day", DAY([Date])
)

I have created another dax variable named “Customdate” to get filtered dates, including the business logic. In this formula, you have seen how we can use one dax variable in another dax variable. After the return part, we have used the Addcolumns dax function. Addcolumns is taking “Customdate” as the table, and it contains one column named “Date.” With the help of this date column, we have created Year, Month No. Month name and day columns.

Mark as date table

Once the Power BI date table is finalized, the next step is to mark this table as a date table. You have to select a newly created date table and go to the table tools. In table tools, click on the option “Mark as date table.” You will see the option to choose which column you need to select for the date. Just select that column and click ok.

It is essential to mark it as “Mark as date table” to use time intelligence functions. Without selecting this option, it is not possible to use time intelligence function in your dax formulas.

Power BI DAX Course(Free)

If you want to get a complete Power BI DAX Learning course, just signup below. You will get an email with the link to the free power bi dax learning course.

For more useful blogs, please visit Learn DAX