I want to do a quick run through of time intelligence patterns commonly used in Power BI. You may watch the full video of this tutorial at the bottom of this blog.
This is covered in detail in the Mastering DAX Calculations course at Enterprise DNA Online. In the course, there are in-depth discussions on how to use these functions and get better insights using time-related data. But for now, I just want to give an overview of how these functions can be utilized.
Different Time Intelligence Patterns
There are three kinds of commonly used time intelligence functions in Power BI – time comparisons, aggregations and information functions.
Time comparisons are used to compare one date or time period to another. Aggregations, on the other hand, include month-to-date, year-to-date and quarter-to-date functions. Information functions usually give straightforward information like the opening balance for the month or the end balance for the year.
I’m going to show a few examples below on how these functions can be applied. Just remember that the key here is still understanding the context behind these functions to get the right kind of insights.
Time Comparison Functions
I’ll start off by creating a new table without anything on it. This will help me showcase the different time intelligence patterns later on.
From there, I can start a simple time comparison calculation.
I’ll use the CALCULATE function, then Total Revenue. I’m also going to use the DATEADD function.
I can select the time frame here by day, month, quarter or year. For this example, I’m going to choose quarter.
I can also decide how far back or how much in advance I want to move in terms of time frames. So, if I want to check for the last quarter, I’ll just put minus 1. I can tweak that any time and put minus 3 or plus 3 too, depending on what data I need. There’s a lot of versatility with this particular time comparison function.
Once I drag that into my table, I can start comparing my Total Revenue with the revenue from last quarter.
This is just one application of time comparison here. There are many other variations, so it doesn’t necessarily just apply to revenue or sales.
Aggregate Functions
Aggregations show the month-to-date or year-to-date data, or anything similar. For now, I’m going to show you how to get the Revenue MTD.
I’ll start off with the CALCULATE function because I’m still calculating the revenue here. The only difference is I’m doing it under a different context this time.
To add that context, I’m going to use the DATESMTD function. This is what closes this measure as an aggregation.
Let me drag that into my table as an additional column.
So this column actually works almost like a Cumulative Total. It will reset every month as new data comes in.
Once you learn how to do one aggregation, it’s easy enough to do them all. In this case, I only need to change DATESMTD into DATESYTD or DATESQTD. From there, I can look at my data in many other ways.
Information Functions
To show what information functions are for, I’ll get the opening balance for the month. I just need to use the OPENINGBALANCEMONTH function, then reference the Total Revenue and the Date column.
I’ll drag that into my table again as an additional column.
I’ll move my date slicer and start from the 31st of October.
The Total Revenue corresponding to that date is 401,289.90.
Since that’s the 31st of the month which is the end of the month, I’ll assume that this is the same amount that will be carried over as the Opening Balance. This is why you’ll see that on the 1st of November, it shows that the Opening Balance is also 401,289.90.
These information functions are perfect for monetary or financial management. It also works well with other time intelligence functions.
For example, you can look at your month-to-date calculations or your aggregations to see how much your opening balance is being impacted by the ongoing activity.
There are also a lot of other information functions on Power BI. There’s CLOSINGBALANCEMONTH, CLOSING BALANCEQUARTER, etc. I just need to start typing “Balance” and the choices will come up.
***** Related Links *****
The DATEADD Function: The Best And Most Versatile Time Intelligence Function in Power BI
Time Comparison For Non Standard Date Tables In Power BI
Time Intelligence In Power BI: How to Calculate The Number of Transactions Made in the Last N Days
Conclusion
Seeing how those time intelligence functions were applied, it’s easy to see that these are helpful in getting as much insight as possible in different scenarios. As I mentioned earlier, this is covered in-depth in the Time Intelligence section of the Mastering DAX Calculations course. I have the link posted to that course below.
A lot of the analysis Power BI users do is time-related, so these are definitely key functions to learn. It’s important to know which functions are appropriate in specific situations especially since these Time Intelligence functions can simplify the measures you create on Power BI.
All the best,
Sam
