Time intelligence in DAX is somewhat of a misnomer. Time intelligence actually does not deal with time in the sense of hours, minutes, seconds, and so on. Instead, time intelligence would be better off being called date intelligence or calendar intelligence, because the time intelligence functions in DAX really deal with dates. Hence, if you are looking for calculations involving time in the sense of hours, minutes, and seconds, proceed to the next chapter. Otherwise, this recipe will demonstrate how to use the time intelligence functions in DAX to perform calculations related to things such as year-over-year, month-over-month, and quarter-to-date.

To prepare for this recipe, perform the following steps:

1. Create a table using the following formula:

R01_Calendar = CALENDAR(DATE(2015,1,1),DATE(2022,12,31))

2. Create a table called R01_Sales by importing the Sales.csv file from the GitHub repository: https://github.com/PacktPublishing/DAX-Cookbook/tree/master/Chapter02.
3. Create a column in the R01_Sales table using the following formula:

Year = YEAR([Date])

4. Create another column in the R01_Sales table using the following formula:

Month = FORMAT([Date],"mmmm")

5. Create a third column in the R01_Sales table using the following formula:

Month Sort = MONTH([Date])

6. Set the Sort by column for Month to Month Sort.
7. Create a bidirectional relationship between the Date column in the R01_Sales table and the Date column in the R01_Calendar table.

To implement this recipe, perform the following steps:

1. Create the following measures:

Opening Balance = OPENINGBALANCEMONTH(SUM('R01_Sales'[Value]),'R01_Calendar'[Date])

Closing Balance = CLOSINGBALANCEMONTH(SUM('R01_Sales'[Value]),'R01_Calendar'[Date])

Month to Date = TOTALMTD(SUM('R01_Sales'[Value]),'R01_Calendar'[Date])

Previous Month = TOTALMTD(SUM('R01_Sales'[Value]),PREVIOUSMONTH('R01_Calendar'[Date]))

Year to Date = TOTALYTD(SUM('R01_Sales'[Value]),'R01_Calendar'[Date])

Previous Year = TOTALYTD(SUM('R01_Sales'[Value]),PREVIOUSYEAR('R01_Calendar'[Date]))

2. Create a Table visualization and place the Date column from the R01_Sales table, as well as all of the measures created in Step 1, in the Values field for this visualization.
3. Create a Matrix visualization and place the Year column from the R01_Sales table in the Rows field.
4. In the same Matrix visualization, place the Month column from the R01_Sales table in the Rows field underneath the Year column.
5. In the same Matrix visualization, place all of the measures created in Step 1 in the Values field.

For the Opening Balance measure, note that all values for January 2017 are blank. This is because there is no ending value for the month previous to January 2017. For February 2017, note that the value of the Opening Value measure is 155.00. In the data, 155.00 is the value of January 31, 2017. Also, note the value for January 2018 dates. The value is 136.92. Note that in the data, this is the value (rounded up to two decimals) of December 31, 2017. Thus, the OPENINGBALANCEMONTH function essentially lists the final value as computed by the expression specified in the first parameter, in this case, SUM([Value]), for the previous month.

The Closing Balance measure utilizes the CLOSINGBALANCEMONTH function. This works similarly to the OPENINGBALANCEMONTH function except that it lists the final value for the month as computed by the expression specified in the first parameter, in this case, SUM([Value]).

The Month to Date measure utilizes the TOTALMTD function. At the day level, you can see that this measure is summing up the current day as well as any previous days in the month since we have specified the computational expression to use SUM([Value). In the Matrix visualization, the value of 1,710.00 is displayed for the Month to Date measure. This value of 1,710.00 corresponds to the Month to Date measure value for the date January 31, 2017. Thus, we can see that the TOTALMTD function computes the value of the specified expression for all days in the current month in context that are on or before the current date in context.

The Previous Month measure also uses the TOTALMTD function with the same calculation expression, SUM([Value]), but, for the dates, specifies the PREVIOUSMONTH function. January 2017 dates are blank because there is no previous month's data. However, the February dates all read 1,710.00, which is the value for Month to Date for January 31, 2017. Hence, using the PREVIOUSMONTH function as the dates clause causes all dates within the previous month, and only those dates, to be included in the calculation of the specified expression. This may or may not be what you intended! For example, what you might have intended was for this measure in February to show the total for the same number of days in January. If this was indeed the intention, then we could have used the DAX function, PARALLELPERIOD, to achieve this.

The Year to Date measure uses the TOTALYTD function. This function works in an identical manner to the TOTALMTD function, except at the year granularity. Similarly, Previous Year makes use of the PREVIOUSYEAR function, which works identically to the PREVIOUSMONTH function except at the year level of granularity.

Perhaps the biggest mistake people make when attempting to use DAX's time intelligence functions is to not utilize a related date table for the dates parameter of the functions. Not utilizing a related table of dates can cause unexpected results. To see how things can go awry, perform the following steps:

1. Create the following measure:

Opening Balance 2 = OPENINGBALANCEMONTH(SUM([Value]),'R01_Sales'[Date])

2. Place the Opening Balance 2 measure in both the Table visualization and the Matrix visualization created earlier.
3. Delete the relationship between the R01_Sales and R01_Calendar tables.

Note that the Opening Balance 2 measure values are all blank in the Matrix table, but are correct in the Table visualization. As already stated, you can get unexpected results when not using a related date table.

For more details, refer to the following link:

• Time-intelligence functions: https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax