When dealing with financial reports, it's often critical to be able to look at specific time periods such as years, quarters, and months. It's also important to be able to compare periods of time such as year-on-year, year-to-date, month-to-date, etc. By using DAX's time intelligence, you can do complex date-based analyses with a single line of code.
Before getting into time intelligence, below are examples of date and time functions that are available to use in Power BI:
Time intelligence applies to a broad array of functions, not some single core cognitive algorithm or something like that. And it's easy to muddy the waters and have it be a bit of a meaningless marketing term if you're not careful. To break it down a bit, we can group time intelligence into three groups of functions.
The first group are single-date functions. These functions return a single date and are the easiest to think about. A good example is the first date function, which takes in the date column and returns the first date, given the filter context. More complicated are the date table functions. These functions return a table as the output. One example is the dates year-to-date function. This function also takes in a date column and returns a column filtered year-to-date. So let's say that today is January 3rd; if you pass in dates limited to last year, it will only return January 1, 2, and 3 for that year. Finally, we have date aggregates. These functions take in an expression and a date column and return a single value from that expression. An example is the total year-to-date. This takes an expression and evaluates it based on the year-to-date values.
Date Tables
To make use of time intelligence capabilities, you need something called a date table. A date table is essentially a table of all the dates between a specific range. These tables often have additional columns, such as month name, year name, quarter, etc. You may be wondering why can't I use the date column on my sales table, for example? Well, one of the things that time intelligence requires is for the dates to be contiguous, which means no gaps. This is rarely the case with native date columns in your source data.
Create a Date Table using DAX:
Date = CALENDARAUTO()Dates = CALENDAR("01/01/2011", "01/01/2021")Extract the year and the month from the Date Table:
Year = Year([Date])
Month = Month([Date])
Month = FORMAT([Date], "MMM")The last formula will format the Month from numerical to "MMM" (Dec, Jan, Feb, etc.)
Applying Time Intelligence
The real power of working with dates in DAX is using time intelligence functions.
These are the functions that are purposely built to support analysis that is very specific to dates, such as cumulative value analysis. And this group of functions is used to figure out things like month‑to‑date, quarter‑to‑date, and year‑to‑date values, for example. And then, there are relative date sets, where you can list the actual dates used to determine month and year‑to‑date, or you can set a range of dates. Maybe you want to compute sales between a beginning and an end date; relative date set functions allow you to define what specific dates you wish to analyse. Relative date functions are helpful for things like year‑over‑year calculations and other types of time period comparisons that are similar to this. The last two types of time intelligence functions are related (End of period values and Balance forward from the prior period). You can look at values for the end of a period, such as an account balance for a customer at the end of the year, or conversely, look at the opening balance at the beginning of the year, month, or quarter. Both of these end‑of‑period values and balance forward values look specifically at month, quarter, and year.
The following calculation uses time intelligence functions to calculate the revenue year-to-date:
Revenue YTD = TOTALYTD([Total Revenue], 'Date'[Date], ALL('Date'))
The following calculation calculates the revenue for last year by using the SAMEPERIODLASTYEAR function:
Revenue Last Year = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
Another calculation that could be useful is year-over-year growth.
YoY Growth = ([Total Revenue] - [Revenue Last Year])/[Revenue Last Year]
