How to Build Time Intelligence into your Power BI Data Models

One of the most crucial aspects of any kind of business intelligence, and for that matter any sort of statistical analysis, is understanding how metrics change over time. Even high-end, critical data points like total product sold become almost useless without previous data points to compare – we don’t know what we should do with those numbers unless compared to prior time period performance. So, naturally, Time Intelligence – or evaluating data models based off historical relative performance – is a crucial part of Power BI to master. 

In a nutshell, Time Intelligence allows you to visualize your data and run calculations based off set time periods, ranging from hours to years. Time Intelligence is powered by DAX – but before you can start using its functions, you’ll need to create a valid Date Table, which can be created through a number of means. You can read a full, up to date guide on how to create a Date Table from Microsoft experts here if you’re not sure how to build one; in brief, you’ll be able to use either Power Query or DAX to generate a new table, clone an existing one, or connect to another data source, alongside the Auto date/time option for simpler requirements. Once your date table is ready, you’ll still need to ensure it’s actually integrated with your data model. To achieve this, navigate to the Relationships View, then create a new Relationship between your Date Dimension and the table you’d like to apply it on. Once the Table and Model are connected, you’re ready to start working with Time Intelligence! 

There’s a number of cool things you can do with Time Intelligence, all related to tracking functions of time. Among the most common functions are TOTALYTD, which evaluates the year-to-date value of the current expression, and SAMEPERIODLASTYEAR, which returns a table of the values for the same calculation, exactly one year back. You can read the full list of DAX-based Time Intelligence functions right here. But how can these diverse time-based functions be useful to you? 

Why use Time Intelligence? 

The most obvious use for Time Intelligence is its ability for comparative analysis. In other words, the selection of time intelligence functions gives you the ability to easily assess the areas in which your enterprise is flourishing and floundering both over the short and long term, relative to its typical historical performance. Uniquely, Time Intelligence functions allow you to build calculations that will let you natively evaluate business performance over any time period you’re interested in, with historic data presented clearly in the same sheet for immediate and visual comparison. 

Proper use of time intelligence will make tracking changing dynamics much easier by enabling you to nearly instantly compare metric performance over time. Time Intelligence will be your go-to for crafting reports that deliver insights around time-based performance, whether you want to compare performance of diverse metrics or simply evaluate year-to-date profits. As long as you’ve got your Date Table ready, you should find using and applying Time Intelligence to get the timely data you need simple and easy. 

Time Intelligence In Practice: A Use Case 

Let’s assume that you’ve successfully brought in your Date Table, having properly filled out the DAX expressions registering Year, MonthNum and MonthName, Day and Quarter, and want to measure the Year-To-Date metrics for, let’s say, gross revenue. To do this, we’d create the following DAX measure with Time Intelligence: 

 GrossRevenue YTD = TOTALYTD(SUM(Data[Gross Revenue]), ‘Date’[Date]) 

 This function will then evaluate the connected Date Table for dates that match the Year-To-Date requirement for Gross Revenue, and then visualize the results within the report. To track Month or Quarter to date, we’d simply replace YTD and TOTALYTD in the equation with MTD/TOTALMTD or QTD/TOTALQTD respectively. And best of all, this equation allows us to easily compare different recorded time periods within the same visualization, allowing you to easily pick up on wider trends with your product or service – such as unusually popular months or business slumps that seem to have a pattern behind them. 

 All in all, Time Intelligence functions are absolutely crucial for getting the most use out of Power BI, and they’re not too difficult either. DAX can help you keep things simple, and with a handy list of all the available functions ready for use, you’ll be able to evaluate time calculations quickly, accurately, and over absolutely any period. Time Intelligence will also provide a helping hand by allowing you to visually compare time periods against one another, making evaluation of key metrics, especially over longer periods of time, very simple. Using Time Intelligence, many basic functions of business reporting can become a lot easier. And now that you know how to build them, you’ll never have to sweat creating a Year-To-Date profits report again!