# Use DAX time intelligence functions

Completed

DAX includes several time intelligence functions to simplify the task of modifying date filter context. You could write many of these intelligence formulas by using a CALCULATE function that modifies date filters, but that would create more work.

Note

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.

### Date table requirement

To work with time intelligence DAX functions, you need to meet the prerequisite model requirement of having 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.

### Summarizations over time

One group of DAX time intelligence functions is concerned with summarizations over time:

Note

While the TOTALYTD function is simple to use, you are limited to passing in one filter expression. If you need to apply multiple filter expressions, use the CALCULATE function and then pass the DATESYTD function in as one of the filter expressions.

In the following example, you will create your first time intelligence calculation that will use the TOTALYTD function. The syntax is as follows:

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


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).

Download and open the Adventure Works DW 2020 M07.pbix file. Then, add the following measure definition to the Sales table that calculates YTD revenue. Format the measure as currency with two decimal places.

Revenue YTD =
TOTALYTD([Revenue], 'Date'[Date], "6-30")


The year-end date value of "6-30" represents June 30.

On Page 1 of the report, add the Revenue YTD measure to the matrix visual. Notice that it produces a summarization of the revenue amounts from the beginning of the year through to the filtered month.

### Comparisons over time

Another group of DAX time intelligence functions is concerned with shifting time periods:

Now, you will add a measure to the Sales table that calculates revenue for the prior year by using the SAMEPERIODLASTYEAR function. Format the measure as currency with two decimal places.

Revenue PY =
VAR RevenuePriorYear = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
RevenuePriorYear


Add the Revenue PY measure to the matrix visual. Notice that it produces results that are similar to the previous year's revenue amounts.

Next, you will modify the measure by renaming it to Revenue YoY % and then updating the RETURN clause to calculate the change ratio. Be sure to change the format to a percentage with two decimals places.

Revenue YoY % =
VAR RevenuePriorYear = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
DIVIDE(
[Revenue] - RevenuePriorYear,
RevenuePriorYear
)


Notice that the Revenue YoY % measure produces a ratio of change factor over the previous year's monthly revenue. For example, July 2018 represents a 106.53 percent increase over the previous year's monthly revenue, and November 2018 represents a 24.22 percent decrease over the previous year's monthly revenue.

Note

The Revenue YoY % measure demonstrates a good use of DAX variables. The measure improves the readability of the formula and allows you to unit test part of the measure logic (by returning the RevenuePriorYear variable value). Additionally, the measure is an optimal formula because it doesn't need to retrieve the prior year's revenue value twice. Having stored it once in a variable, the RETURN clause uses to the variable value twice.