Working with Dates in Power BI

In Thailand, we use various date formats, including both the Buddhist Era (B.E.) and the Gregorian Era (C.E.). In this article, we will guide you on how to import date data into Power BI and manage dates in different formats.

You can download the sample dataset at this link.

First, we need to understand the Date data type in Power BI. If you look at the Data Pane on the right side of Power BI, and if set correctly, there will be a drop-down menu that allows you to view data by year, quarter, month, or day. However, there might be cases where you have more than one date column or table, such as shipment dates and payment received dates. In this case, it’s best to create a separate Date Table and let Power BI treat the dates in the Date Table as a Date Hierarchy rather than individual columns or fields.

How to Create a Date Table in Power BI

1. Go to the Data View and select New Table.
2. Name the table, for example, date table, and write the formula = CALENDARAUTO() as shown below.
3. Then click “Mark as date table” to make this table a Date Table.
   Besides using CALENDARAUTO(), which automatically sets the start and end dates, you can also use the CALENDAR() function to manually specify the start and end dates. The data format to input is DATE(year, month, day).
   
   For more details, visit Microsoft’s guide on model date tables.After creating the Date Table, you should create a relationship between the date table and your main dataset in the Relationship View. (You can read more about combining tables in Power BI here).

Using a Date Table When You Have More Than One Date Column

If you have more than one date column, we recommend creating a relationship but unchecking the box that says “Make this relationship active” to make it an inactive relationship, represented by a dashed line. When you need to use it, use the USERELATIONSHIP() function instead.

Creating Month Names in the Date Table

Typically, we prefer to create day names, month names, etc., in the Date Table to meet our needs. The formula we use is Format([Date],”<desired format>”). Remember to include quotation marks around the desired format.

Below are some examples of date formats:

For instance, creating a column as Mmm YYYY will result in Jan 2013, and YYYYMM will result in 201301.

Creating these columns will be very useful for filtering data in the desired formats.