1. Time intelligence in Power BI
Time to dive into time intelligence functions! When you are calculating values over time, Power BI needs to know where dates are stored in your data model. Marking a table as a date table is done by right-clicking the date dimension, and then selecting “Mark as date table”.
You then select the date key containing a continuous range of dates.
Power BI will then create date hierarchies under the hood that allow you to create correct visuals and calculations.
This report shows a table and line chart with actual sales by year.
If you want to compare a current sales value with the value from one year ago, you can use the SAMEPERIODLASTYEAR() function. Let’s create a new measure, Actual_Sales_Lastyear, to calculate the actual sales from last year. Note that I use the Datekey from the date dimension, which I marked as the date table in a previous step.
If I then add this measure to the table, you can see that it shows the values of the previous year in the new column. When I drill down, this also works on a quarterly or monthly level. For example, the actual sales last year of January 2018 correspond with the sales of January 2017.
Another common set of time intelligence functions are the month-, quarter-, and year-to-date functions. They all evaluate an expression, such as the sum of sales for example, from the beginning of the month, quarter, or year, until today.
Let’s create for example the running sum of the actual sales since the beginning of each quarter. The TOTALQTD() function takes two arguments. The first one is the expression, actual sales in this case, and the second is the date from the date dimension.
The QTD function sums the actual sales cumulatively each month until the end of the quarter is reached. You can see for example that the QTD sales for February 2017 is 864K, which is the sum of sales in January and February, 410K and 454K respectively. In April, at the start of Q2, the running sum starts again.
Finally, let’s briefly discuss an alternative way to explore your data without writing DAX. The Q&A feature uses natural language that is translated behind the scenes to create visuals. Q&A covers a wide range of functions, including time intelligence functions.
To use it, simply add the Q&A visual to the canvas. In the text box, you can start typing what you want to see visualized. As an example, let’s type “Actual sales YTD by dim date datekey”. A visualization pops up. You can see that the actual sales go up as the year progresses, and go back at the start of a new year, which is the definition of a year-to-date function.
Note that this feature has its limitations though. You need to type in the corresponding name of the measure or column for it to work. In this case, the visualization only worked because there is an actual sales YTD measure in the report. So, naming your measures appropriately is vital if you plan your audience to make use of this Q&A feature. You’ll get the chance to play with this during the exercises. Have fun!
2. Let's practice!