There are many cases when you will need to create a date table within Power BI desktop. This could be as simple as creating a master date table or more complex such as creating a monthly or weekly index number tied to a date. To create a date table there are two methods for creating a date table. Method one, create the table directly in the Power BI Desktop, or method two load the date table from the data source.
For this tutorial we will walk through a couple different examples that are specifically addressing creating a date calendar via DAX expressions.
Let’s begin by making a basic table. Open Power BI Desktop, on the Modeling ribbon click New Table.
In the formula bar enter the following DAX expression:
Dates = GENERATE ( CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ), VAR currentDay = [Date] VAR day = DAY( currentDay ) VAR month = MONTH ( currentDay ) VAR year = YEAR ( currentDay ) RETURN ROW ( "day", day, "month", month, "year", year ) )
This generates a simple date table. Let’s walk through what is happening here.
- The CALENDAR DAX function generates a table with a list of dates from Jan 1 to Dec 31 of 2017.
- We define variables (denoted by VAR) to capture details from the column named [Date] that is created by the CALENDAR function.
- The Return function generates one row at a time. The row iterates through each [Date] item in the list which was created by the CALENDAR function. Variables are re-calculated for every row execution.
Note: When creating DAX tables as we are doing so in this example, the DAX table only refreshes when the report refreshes. Thus, if you want the date list to increase over time, or your using a NOW() in the DAX table you will need to be sure to schedule refreshes for the Power BI report in the PowerBI.com service.
By contrast we can also generate the same data table by calculating our data column by column. Again, on the Modeling ribbon click the New Table icon and add the following DAX:
Dates 2 = ADDCOLUMNS( CALENDAR( DATE( 2017, 1, 1) , DATE(2017, 12, 31) ), "day", DAY([Date]), "month", MONTH([Date]), "year", YEAR([Date]) )
While this is great, we have a date table now, but what we lack is flexibility and automatic time intelligence. One option to change this table to auto detect dates within your data model is to replace the CALENDAR DAX statement with CALENDARAUTO().
To use CALENDARAUTO we need to supply a table with a column of dates. We will quickly create a dummy data table with a couple of dates, so we can use CALENDARAUTIO.
Click Enter Data on the Home ribbon. Enter the following information into the Create Table screen. Click Load to add this data to the data model.
Now that we have loaded a table into the model with two dates, we can add our new date table. On the Modeling ribbon click the New Table icon and add the following DAX:
Dates 3 = GENERATE ( CALENDARAUTO(), VAR currentDay = [Date] VAR day = DAY( currentDay ) VAR month = MONTH ( currentDay ) VAR year = YEAR ( currentDay ) RETURN ROW ( "day", day, "month", month, "year", year ) )
Note: In the MyData table we added two dates, 3/3/2017 and 10/30/2017. When we look at the included dates in the new Date 3 table we have every date listed from January 1 to December 31st. This is because the DAX function CALENDARAUTO will return the entire year of calendar dates even if it only finds one date within a given year period of time.
Let’s say we want to build a date calendar that will automatically grow and change over time. We want to identify today’s date and then create a list of dates for the previous year.
Moving back to generating a date table by rows we can now use the DAX NOW function. On the Modeling ribbon click the New Table icon and add the following DAX:
Dates 4 = GENERATE ( CALENDAR( DATE( YEAR( NOW() ) - 1, MONTH( NOW() ), DAY( NOW()) ), NOW()), VAR currentDay = [Date] VAR day = DAY( currentDay ) VAR month = MONTH ( currentDay ) VAR year = YEAR ( currentDay ) RETURN ROW ( "day", day, "month", month, "year", year ) )
Note: In this DAX table we used the NOW() function which returns a date and time. The same can be done when using the TODAY() function which only returns the date and not the time.
This now generates is a date table that starts one year ago and populates all the dates until today. For example, if today is 10-29-2017, then the date list would start at 10-29-2016 and end on 10-29-2017. Pretty cool…
Let us move further down the rabbit hole. We can also start adding calculations that helps us move through date time calculations. For example, you may want to calculate this month’s total sales and possibly last month’s sales. By adding columns with an index, you can quickly shift time periods. Doing so makes time calculations much easier.
On the Modeling ribbon click the New Table icon and add the following DAX:
Dates 5 = GENERATE ( CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()), VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday VAR currentDay = [Date] VAR days = DAY( currentDay ) VAR months = MONTH ( currentDay ) VAR years = YEAR ( currentDay ) VAR nowYear = YEAR( TODAY() ) VAR nowMonth = MONTH( TODAY() ) VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1 VAR todayNum = WEEKDAY( TODAY() ) VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) ) RETURN ROW ( "day", days, "month", months, "year", years, "day index", dayIndex, "week index", weekIndex, "month index", INT( (years - nowYear ) * 12 + months - nowMonth ), "year index", INT( years - nowYear ) ) )
Note: The DAX equation above will work in your report without any changes. However, I made a variable called startOfWeek. This variable allows you to define the start day of the week. For example, if you data starts a new week on Sunday, then the startOfWeek number will be a 1. If your data start of week begins on Wednesday then the start of week number would be a 4. This allows you to auto detect the day of the week and then automatically arranges all your weekly index numbers in the correct format. Try playing around with this variable to see how DAX table changes.
So why work so hard on the date table? Well by having a robust date table you can simplify many of your measures that you need to build for your report. Consider the following example:
You have a Sales table with a date and sales column.
And you have our fancy Dates 5 Table we created earlier:
The Date 5 table is linked to the Sample Sales table:
You can now build the following DAX measures inside the Sample Sales table:
Total Sales = SUM( 'Sample Sales'[Sales] )
Last Week Sales = CALCULATE( [Total Sales], ALL('Dates 5'), 'Dates 5'[week index] = -1 )
If you want to calculate something crazy like the last 5 weeks of sales you can calculate the following:
Last 5 Weeks Sales = CALCULATE( [Total Sales], ALL( 'Dates 5' ), AND( 'Dates 5'[week index] <= -1, 'Dates 5'[week index] >= -5 ) )
The nice thing about these measures is that every time the data set refreshes the dates will automatically recalculate the last week and last five weeks.
If you want to be able to handle the additional filter context of the visual, you can pick up the visual filter context using variables (VAR). Then you can RETURN a calculate function that will shift all your time ranges for you.
Moving Last Week Sales = VAR filterTime = SELECTEDVALUE('Dates 5'[week index], BLANK()) RETURN CALCULATE( [Total Sales], ALL( 'Dates 5'[Date] ), 'Dates 5'[week index] = filterTime - 1 )
Same goes for a moving sum of the last five weeks of sales.
Moving Last 5 Weeks Sales = VAR filterTime = SELECTEDVALUE('Dates 5'[week index], BLANK()) RETURN CALCULATE([Total Sales], ALL('Dates 5'[Date]), AND( 'Dates 5'[week index] <= filterTime -1, 'Dates 5'[week index] >= filterTime -5 ) )
Well that is about it. Thanks for following along.
I am so thankful you have taken the time to read my tutorial. My hope is that by using these free tutorials you can become a rock-star at work. In order to keep these tutorials free please consider purchasing the Power BI Desktop file for this tutorial. Come on it’s only a dollar, I mean you spent than that on your coffee this morning.
You can pay with your PayPal account or via credit card
Hello, Nice calendar. I made a similar calendar that only has one date column that I use in a slicer as a filter. Works well but when I click on the date a calendar box pops up and starts with the first date of the calendar set. Any way I can make that calendar pop up with today’s date by default?
I am not aware of forcing the pop up calendar to default to a certain date. At this time that functionality is not supported.
The Calendar function’s lowest resolution level appears to be at the daily level. How might a date-time table be constructed, using Calendar or other DAX function, to expand each day to the sub-hourly resolution level? Specifically, I would like to be able to add two columns that identify the corresponding 24 hourly and 5-minute interval (e.g. 5,10,15,20…) resolution for each day. Any suggestions on how to construct would be much appreciated.
Wow, this is a great question. I have not thought of this before. From what I currently know, I don’t think building a date table in DAX would be your best method. Instead, I would solve this by making a date table in your data source, or by making it in the Query editor. There are many more functions available that handle dates & times.
Date Functions can be found here: https://msdn.microsoft.com/en-us/library/mt296606.aspx
Date Time Functions can be found here: https://msdn.microsoft.com/en-us/library/mt296608.aspx
If I am filtering my data based on a fiscal year (nailed that formula!) how do I sort columns where I’m showing data by month so they sort properly?
If your using the date hierarchy sorting by months should automatically sort accordingly by month. If you are not using a date column you can tell Power BI to use a sort by column so that he months are sorted by chronological order and not alphabetical order. You can read about this on this article: https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
I just purchased the PBI DAX Date Tables.pbix for $1. Will the file come to me via email or what do I need to do to get it?
You should be able to download it at the following location: https://powerbi.tips/shop/my-account/orders/ once you have purchased the item. You can visit your orders page as many times as you like and download the file as many times as you want. If you are having issues downloading the file, please e-mail me directly at [email protected] and I’ll get you what you need. Sorry for the confusion.
I’ve followed this and for an unknown reason, October 1 doesnt appear and is replaced by Sept 30, 11pm.
Does anyone have the same issue with me?
I just check the Dates 5 DAX and I am able to see October 1st in the date table. If you are joining the date table with existing data, you might not have all the dates listed in your visual. This is because when you pull data together that has been linked on the data will show that is connected via a relationship. In your visual you might want to adjust the date column and change the settings to “Show Items with No data”
Hi im getting an error when copying your code. It says the syntac for “1.” is incorrect.
Same is when using your monthly calender. There it says the syntac for “inc.” is incorrect. Can you tell me what i am doing wrong ?
Not quite sure, I haven’t seen this before, can you post the DAX you are using in the comments? I’ll take a look and get back to you.
Hi Mike, first code where it says “Syntax for ‘1.’ is incorrect is the following:
CALENDAR ( DATE ( 2017, 1, 1 ); DATE ( 2017, 12, 31 ) );
VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
“year”; year )
The second where it says “Syntax for ‘inc.’ is incorrect is from the start of the monthly calander and is :
Start of Month Dates =
VAR inc = [Value]
RETURN ROW (
I am experiencing this same problem. Please advise?
I have tried this on a number of different computers. Here is what I think might be happening. Your keyboard might be pasting in the wrong text strings into the DAX window. Instead of copy and paste try to type in the formula exactly as you see it on the site. This was recently encountered when I was working with individuals from Brazil. See if that helps. Sorry your having issues.
Can you maybe help me.
I have made table dates 5.
I used it with this code:
Previous sales = CALCULATE([TotalSales];ALL(Dates);Dates[year index]-2 )
1. I do not know why i have this seperators : ‘;’
You have ‘,’
2. when i use the command above, the amount is exactly the same as the totalsales amount,
nothing is calculated, really weard, you can explain this?
This is a difference between US and Europe keyboards. The ‘;’ is sometimes surfaced in Europe countries. The DAX engine requires the ‘,’ separator to work properly.
How can I use this to auto insert the new date into the table every day?
If you schedule this report to refresh every day, then when the queries run, it will automatically add the new days to your date table. Every time the report refreshes it updates all the date ranges.
Have there been any changes? I’ve been using Dates 4, successfully for a while now … now all of a sudden its giving me the current year from 1 JAn to 31 Dec rather than Dates year to date?
Hello Mike, Great article by the way.
I have integrated this syntax and are using the Calendar[Min_Year_Start_Date],[Max_Year_End_Date] from our fiscal year table in order to scale ahead and only add the Fiscal year dates. The issue I am facing is when the week index hits 0, it adds to many dates to the current week (13 actually) and ahead +1, +2, +3 etc. are out of sync with the start of week. Is there a way to set the week index to read ahead accurately?
Thanks in Advance.
I haven’t done much testing looking at future weeks of data. I’ll have to take a look at the code. Here is another date calendar, much larger in size but this calendar should be able to produce future dates with week numbers. https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/