557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|Creating a calendar or date table in Power BI using built-in calendar functions|
|Learn how to create a basic date table in Power BI, using either the CALENDAR or CALENDARAUTO functions, and also learn how to then add custom columns of your own.|
Date tables are useful for most data models, and are compulsory for using time intelligence functions:
An example of a basic date calendar with different granularities.
Assuming you haven't created one in another programme, choose New Table from the Modelling tab:
New Table assumes that you will use a function to create a table (such as a date table, or a summary table using aggregates).
There are two main functions for creating the date table. CALENDARAUTO() extrapolates to the start of the first financial year in the model and to the end of the last:
The optional argument FiscalYearEndMonth can change which month is the last in your financial year. The default is 12, meaning that December is the end of the year.
This creates a list of all the dates in yoru model, which should look like this:
I'd recommend naming the new column to anything but Date since this is a command word. Try Date Key or Reference Date.
The drawback of this function is that it doesn't contain any future dates, which could hinder calculations including forecasting. As an alternative, you could use the CALENDAR() function:
Indicate the earliest date and latest date you will need and Power BI will create a list of all the dates in between. Using the month name avoids the risk of American dates.
Now to add all the columns that we might need into the table. First up, add the names of the weekdays:
FORMAT can be used (as for Excel custom dates) to extract parts of the date. In this case DDDD returns the weekday (DDD would return the shortened weekday).
While there are functions for YEAR, MONTH, DAY and others, it isn't necessary to use them. Add a new column equal to the Date Key:
When referencing a date column a drop down appears with the available parts to extract, such as the Date from a date/time column or MonthNo to show 1 instead of January.
Repeat this action for all the options available apart from Quarter, to get something like this:
You can now reference these columns in calculated columns, measures, and in visuals (including slicers).
Missing from the above list is the quarter for each given date, due to there being a problem with the value returned:
The Quarter is based by default on a year that starts with January, but what happens if your year starts in April?
Rather than use a complicated SWITCH function, a quicker way to proceed is to create the column using this code:
Interestingly despite giving the start of the year within CalendarAuto, no calculations take this into account by default.
25 Aytoun Street