3G. Use DAX time intelligence functions in Power BI Desktop models

Question 1

What is time intelligence?

Answer 1

Time intelligence relates to calculations over time. Specifically, it relates to calculations over dates, months, quarters, or years, and possibly time. Rarely would you need to calculate over time in the sense of hours, minutes, or seconds.

In Data Analysis Expressions (DAX) calculations, time intelligence means modifying the filter context for date filters.

Question 2

What kind of questions can time intelligence calculations help you answer?

Answer 2

• What’s the accumulation of revenue for the year, quarter, or month?
• What revenue was produced for the same period last year?
• What growth in revenue has been achieved over the same period last year?
• How many new customers made their first order in each month?
• What’s the inventory stock on-hand value for the company’s products?

Question 3

What if you have irregular time periods and want to filter by them?

Answer 3

Many DAX time intelligence functions are concerned with standard date periods, specifically years, quarters, and months. If you have irregular time periods (for example, financial months that begin mid-way through the calendar month), or you need to work with weeks or time periods (hours, minutes, and so on), the DAX time intelligence functions won’t be helpful. Instead, you’ll need to use the CALCULATE function and pass in hand-crafted date or time filters.

Question 4

What is a model prerequisite for time intelligence, and how do you satisfy it?

Answer 4

You need to have at least one date table in your model. A date table is a table that meets the following requirements:
- It must have a column of data type Date (or date/time), known as the date column.
- The date column must contain unique values.
- The date column must not contain BLANKs.
- The date column must not have any missing dates.
- The date column must span full years. A year isn’t necessarily a calendar year (January-December).
- The date table must be indicated as a date table.

Question 5

What time intelligence functions can summarize over time?

Answer 5

• DATESYTD - Returns a single-column table that contains dates for the year-to-date (YTD) in the current filter context. This group also includes the DATESMTD and DATESQTD DAX functions for month-to-date (MTD) and quarter-to-date (QTD). You can pass these functions as filters into the CALCULATE DAX function.
• TOTALYTD - Evaluates an expression for YTD in the current filter context. The equivalent QTD and MTD DAX functions of TOTALQTD and TOTALMTD are also included.
• DATESBETWEEN - Returns a table that contains a column of dates that begins with a given start date and continues until a given end date.
• DATESINPERIOD - Returns a table that contains a column of dates that begins with a given start date and continues for the specified number of intervals.

Question 6

TOTALYTD is limited to passing in one filter expression. What can you do if you need more than one filter expression?

Answer 6

Use the CALCULATE function and then pass the DATESYTD function in as one of the filter expressions.

Question 7

What is common to all time reference functions?

Answer 7

A reference to the date column of a marked date table.

Question 8

Describe the TOTALYTD function, and its optional expressions.

Answer 8

TOTALYTD(<expression>, <dates>, [, <filter>][, <year_end_date>])</year_end_date></filter></dates></expression>

The function requires an expression and, as is common to all time intelligence functions, a reference to the date column of a marked date table. Optionally, a single filter expression or the year-end date can be passed in (required only when the year doesn’t finish on December 31).

Question 9

What are some time intelligence functions for shifting time periods?

Answer 9

• DATEADD - Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current filter context.
• PARALLELPERIOD - Returns a table that contains a column of dates that represents a period that is parallel to the dates in the specified dates column, in the current filter context, with the dates shifted a number of intervals either forward in time or back in time.
• SAMEPERIODLASTYEAR - Returns a table that contains a column of dates that are shifted one year back in time from the dates in the specified dates column, in the current filter context.
• Many helper DAX functions for navigating backward or forward for specific time periods, all of which returns a table of dates. These helper functions include NEXTDAY, NEXTMONTH, NEXTQUARTER, NEXTYEAR, and PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER, and PREVIOUSYEAR.

Question 10

What are some time intelligence functions that return a single date?

Answer 10

For example the FIRSTDATE and the LASTDATE DAX functions return the first and last date in the current filter context for the specified column of dates.

Question 11

What is the difference between defining date ranges by running either BLANK() through a time intelligence function, or using FIRSTDATE or LASTDATE instead?

Answer 11

For example, the DATESBETWEEN function returns a table that contains a column of dates that begins with a given start date and continues until a given end date. When the start date is BLANK, it will use the first date in the date column. (Conversely, when the end date is BLANK, it will use the last date in the date column.) In this case, the end date is determined by the MAX function, which returns the last date in filter context.

Question 12

What is a quick and easy way to add or subtract days to a time period?

Answer 12

Because Microsoft Power BI internally stores dates as numbers, you can add or subtract numbers to shift a date.

Question 13

What are snapshot values, and what should you remember about how they summarize?

Answer 13

Common examples include inventory stock levels or account balances. A snapshot of values is loaded into the table on a periodic basis.

When summarizing snapshot values (like inventory stock levels), you can summarize values across any dimension except date. Adding stock level counts across product categories produces a meaningful summary, but adding stock level counts across dates does not. Adding yesterday’s stock level to today’s stock level isn’t a useful operation to perform (unless you want to average that result).

Question 14

What is one way of summarizing snapshot tables that prevent accidential SUMing?

Answer 14

When you are summarizing snapshot tables, measure formulas can rely on DAX time intelligence functions to enforce a single date filter. For example, filter by LASTDATE to only show stock balance for the last date of a given period.

Question 15

What is counterintuitive about using CALCULATE with a LASTDATE filter to show the last recorded daily stock balance, and why?

Answer 15

Notice that the measure formula uses the SUM function. An aggregate function must be used (measures don’t allow direct references to columns), but given that only one row exists for each product for each date, the SUM function will only operate over a single row.

Question 16

What issue can arise when using LASTDATE or FIRSTDATE filters on summary measures to display snapshot values, and how can you prevent that issue?

Answer 16

Filtering by the last date in filter context has inherent problems: A recorded date might not exist because it hasn’t yet happened, or perhaps because stock balances aren’t recorded on weekends.

You can prevent this issue by adjusting the measure formula to determine the last date that has a non-BLANK result and then filter by that date. You can achieve this task by using the LASTNONBLANK DAX function.

Question 17

What are examples of iterator functions, and how do they work?

Answer 17

The LASTNONBLANK function is an iterator function. It returns the last date that produces a non-BLANK result. It achieves this result by iterating through all dates in filter context in descending chronological order. (Conversely, the FIRSTNONBLANK iterates in ascending chronological order.) For each date, it evaluates the passed in expression. When it encounters a non-BLANK result, the function returns the date. That date is then used to filter the CALCULATE function.

Question 18

Why should the LASTNONBLANK function (and maybe all iterator functions??) be used inside CALCULATE?

Answer 18

The LASTNONBLANK function evaluates its expression in row context. The CALCULATE function must be used to transition the row context to filter context to correctly evaluate the expression.