1. The Date table
Welcome back! In this chapter we will be looking at dates in Power BI. We'll learn about the importance of having a stand alone date table, as well as how to use date and time-intelligence functions in DAX. We'll finish off this chapter by learning how to create Quick Measures.
2. Working with dates
Power BI has 100s of built in functions available. We'll walk through some example date and time, format, and time intelligence functions using the date 20th September 2020 at 12.52pm as our datetime argument.
Using some of the date and time functions, we can pass through our datetime. For the YEAR function it will return 2020, Quarter returns 3 and Month returns 9.
However, on some occasions we want dates formatted in ways that the current date and time functions cannot provide. For example, if we want the weekday name of the date we need to use the FORMAT function - where we pass our argument and the desired format expression - in this case we will type d four times. An exhaustive list of all format functions can be found in the DAX documentation.
Power BI also has various time-intelligence functions available, but we'll look at them later when creating Quick Measures.
3. Working with dates
Working with dates in your reports is very common, there will always be occasions where we need to evaluate data in a time-series to be able to spot trends and patterns.
Out of the box, DAX has useful functions and features that support working with dates. It contains over 20 date and time functions, over 30 time intelligence functions, and it is able to generate date hierarchies to drill down on visualizations.
4. The importance of a date table
Date hierarchies can be very useful, but there are some pitfalls that occur when relying on dates from a transactional table like Sales.
Time intelligence functions rely on having dates in a table that contains no gaps. For example, if our Sales table had no transactions for a particular day, then the function which called it will return the wrong result.
This is important to consider because it will not return an error, just the wrong result making it difficult to troubleshoot.
5. Creating a Date Table
A dedicated date table in your model is highly recommended for accurate reporting, particularly if you'll be using time-intelligence functions.
There are many benefits for using a dedicated date table. You can filter across multiple date attributes such as Month and Year. You can create custom calendar views and definitions to match your financial year. Additionally, you can use time-intelligence features to select a time horizon such as Last 30 Days.
You can then apply this to many types of analysis including revenue by day of week, fiscal and public holiday performance.
6. Creating a Date table
Let's learn how to create our date table. In DAX, there are two ways to achieve this.
First, we can use the CALENDAR function. It takes two arguments - a start and end date, and returns a table with a single column containing a list of dates inclusive of the specified dates.
In this example, we are creating a date table for dates between 1st January 2020 to 31st December 2020.
7. Creating a Date table
8. Creating a Date table
The other function we can use is CALENDARAUTO.
Behind the scenes it calls the CALENDAR function. Power BI will automatically provide the earliest and latest date in the model as arguments. However, fiscal_year_end_month needs to be specified for the last fiscal month you want to see after the last date. For example, if the last date was '2020-07-27' and we specified 12, we'd see dates until the end of the year.
9. Creating a Date table
10. Let's practice!
Now that we've learned how to work with dates, let's practice!