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 are already available in any table.

Importance of Date

As we know, the date is an important part of 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 target 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 Blank query you can write query for table or data without any data connection. You can write direct statement in M language which is ready for data in blank query.

Open Power BI and Click on Get Data-Blank Query

If you will follow this step then 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 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 number 1 and 2 should be 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 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 to the 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 table from existing table as below in image. As you will follow the step 1 and 2 and 3 and will enter the date table will be created with dates as in 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 minimum date and "31/12/2017" as maximum date. After clicking on 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 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 as below in screen. You can delete the relationship on clicking delete option.



After double clicking on the relation you can see the details of 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 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 slicer field you can drag and drop the column to slicer field.

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

Summary

So far we have learned the importance of date table 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.

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