Time intelligence

There are probably hardly any Power BI models that do not include some analysis over time. We want to compare current results with those of last year, for instance. Many other calendar-related insights may also be needed, like year-to-date results, rolling totals, or growth rates from any other past period of time. The difficulty is that the Gregorian calendar is quite messy: most years have 365 days, but some have 366, and months can have anywhere between 28 and 31 days.

These calendar complexities notwithstanding, calendar-based analysis is simply about filtering to change context. Consider the year-to-date sales chart below:

Figure 1.4.9: A year-to-date chart

By definition of year-to-date, what you see in, say, the August column, is the total sales in the period of January 1, 2021 up until August 31, 2021. However, the query context for this column contains a filter on year (2021) and month (August), causing a selection of August 1 to August 31...