Today I am going to explain an important topic i.e. how to add continuous set of dates in Power BI. When I talk about continuous date 1st question that will come in our mind is why do we need a continuous dates?

Let’s understand need of continuous date or calendar table.

If there is a date field in our data model we should always add Calendar table. Reason for this are below:

1. Time Intelligence function. Whenever we are required to use time intelligence functions, we should have a continuous date table.
2. Look up table. This table will work as a lookup table for our business questions.

Now next point is how we can add date table in our data model.

We can Import date table from our data warehouse, if we do not have any existing date table in that case, we can use DAX functions to create date table.

Now I will explain step by step how to add date table (Calendar) using DAX as below . If you are interested in more topics on Microsoft Power BI , you can visit our Blogging Website – https://www.technicaljockey.com/microsoft-power-bi-analytics/

—–

We have CALENDAR function in DAX to add continuous date column. We are adding a calculated table using this function.

Go to Modelling tab and click on New Table:

We are selecting New Table as we are going to add a calculated table using DAX functions.

I will explain two functions to add calculated date table in Power BI:

1. CALENDAR(<start_date>, <end_date>): this function will add continue date rage from given starting date to given end date.

e.g. Date = CALENDAR (DATE (2014,01,01),DATE(2018,01,31))

Calendar will start from 01 January 2014 to 31 January 2018

Last date in date column:

2. CALENDARAUTO([fiscal_year_end_month]) :This function returns date column starting date will be minimum date from data model and last date will be last date of fiscal year of maximum date present in data model.

         e.g.    let say in my data model Sample Superstore we have date range from 03 January 2014 to 05 January 2018.

CALENDARAUTO() : Date column start from 01 January 2014 to 31 December 2018

CALENDARAUTO(3):  Date column start from  01 April 2013 to 31 March 2018

Go to Modelling tab > click on New Table

Add CALENDARAUTO(3) DAX function

Date column will be added with start date as 1st April 2013

Lat date is 31 March 2018

Now we have added continuous date column in our data model, Next step is to add more fields related to date table like days,month ,year etc.

Add Month Calculated Column:

Month name calculated column:

Year Calculated Column:

Add Day Calculated Column:

Same we can add Quarter calculated column:

Now we have all Date table with us and we can create relationships between tables and use in creating visualizations.

Relationship:

Create a Donut chart showing Profit according to Month Name: