Working With Dates And Date Table In Power BI

Introduction

 

In this article, we will learn and implement the different ways of creating and working with date in Power BI. We will see how to create date table in Power BI from DAX function, direct query, and from the table date which is already available in any table.

 

Importance of Date

 

As we know, the date is an important part of the analysis in business analytics. For example, if we have data available of sales, purchase, target, achievement in a table in date format then we want to analyze that data month-wise, year-wise, quarter wise and so on. We also set some targets in any organization based on the year.

We also forecast some appropriate data based on previous dates.

 

Lets now discuss the implementation of date table and date...

 

Creating date table from Blank Query

 

 

In the Blank query, you can write queries for tables or data without any data connection. You can write a direct statement in M language which is ready for data in a blank query.

Open Power BI and Click on Get Data-Blank Query

 

If you will follow this step then the connect button will not appear and it will directly open a new query editor window.

 

 

You can also click on Get Data - Other - Blank Query - Connect

 

 

After following either of the above two steps a new query editor window will appear as below. Here you have to click on Advance Editor Option which opens a new advance query editor as below.

 

 

Copy the query and paste it in the box and click on Done button. I have highlighted some important point by numbers.

 

In below screen point 1 and point 2 is the table name. Table name in both place as in Point numbers 1 and 2 should be the same, otherwise, it will give error. Point 3 is the syntax checking result which is done by Power BI automatically. Point 5 is the link of Power BI document where you can read more about adding a custom column in Power BI desktop.

 

Below is that link here.

 

Now click on Point number 4 which is the Done button as shown in screen below.

 

 

Once you click on Done the following window appear where you can select the date range for your date table.

 

 

Now you are at the final step of creating date table.

 

Now click on the final step of creating date table from a blank query. Click on Invoke button. Now the date table is already created as below. You can now use it wherever you want to use.

 

 

Creating date table from Existing Table

 

I have an excel sheet with transaction detail which contains “Receipt Date” as date column now I have imported that sheet into Power BI.

 

To know more about how to work with excel sheet in Power BI let us have a look at my previous article.

 

Now we will follow the step for creating date table from Power BI existing table or query.

 

 

Transaction Detail Table

 

Click on Modeling - New Table a new area will appear where you can write DAX function for creating a table from the existing table as below in the image. As you will follow step 1 and 2 and 3 and will enter the date table will be created with dates as in the Transaction Detail table.

 

 

Creating date from Hard Code date

 

If you have to work with fix date range and that range is known to you then you can create table with the step as you have followed above.

 

Click on Modeling - New Table

 

I have taken DateTableFromHardCodeDate as table name and “01/01/2017” as the minimum date and "31/12/2017" as maximum date. After clicking on the enter button the date table with date data will be created.

 

 

Changing the date format

 

To change the date format as per your convenience click select the date column first and select the data type and then select the format as below.

 

 

Different date format as below in below screen

 

 

So far, we have seen how to create date tables now let’s move further.

 

Working with Date table

 

Now we can give reference of date table with Transaction table. It is used as slicing and filtering the table data with that date range.

 

To set the reference of date table with transaction detail table click on Relationships this is in the left side of the Home tab. After clicking on the relationships table the following screen will appear.

 

 

Now click on DateTable Date column and drag it into the TransactionDetail table Receipt Date Column as below

 

 

Once it is done you can see the relationship on selecting or clicking the relation line as below. The relation will be highlighted below in the screen. You can delete the relationship by clicking the delete option.

 

 

After double-clicking on the relation you can see the details of the relationship between those tables here you can edit the relationship also. Here you can see whether the relationship is active or not. You see the filter direction also here.

 

 

Slicing with Date table value

 

Now you have successfully created the date table and also mapped it with TransactionDetail table.

 

Now go to visualization pane and select a table from available Power BI VISUALIZATIONS.

 

Drag and drop the value on clicking the table from the transaction table. Again copy the table and click on the slicer option available in VISUALIZATIONS. Change the value of Slicer from Date table and the slicer will be created.

 

 

Now you can filter the table data from the slicer which has been created from the Date table date column.

 

Note, in slicer, you can see the date range. I have highlighted the slicer field you can drag and drop the column to slicer field.

 

Now as per the selected date range the table data will be filtered as I have filtered it below

 

 

Summary

 

So far we have learned the importance of date tables in analytics. We have implemented step by step to create date table in Power BI. We have also learned how to format the date column and how to set date table relationship to another table. Further, we have learned how to create slicer and filter with that date table.

 

I hope you will learn and enjoy from this article. You’re welcome with like, comment, share, and any type of suggestion for my appreciation.