Automatic Date/Time in Power BI

Power BI has a feature called Auto Date/Time, which can be configured through the options in the Data Load section (see Figure 8-1).

When the setting is enabled—it is by default—Power BI automatically creates a date table for each Date or DateTime column in the model. We will call it a “date column” from here on. This makes it possible to slice each date by year, quarter, month, and day. These automatically created tables are hidden to the user and cannot be modified. Connecting to the Power BI Desktop file with DAX Studio makes them visible to any developers curious about their structure.

The Auto Date/Time feature comes with two major drawbacks:

• Power BI Desktop generates one table per date column. This creates an unnecessarily high number of date tables in the model, unrelated to one another. Building a simple report presenting the amount ordered and the amount sold in the same matrix proves to be a real challenge.

• The tables are hidden and cannot be modified by the developer. Consequently, if one needs to add a column for the weekday, they cannot.

Building a proper date table for complete freedom is a skill that you learn in the next few pages, and it only requires a few lines of DAX code. Forcing your model to follow bad practices in data modeling just to save a couple of minutes when building the model for the first time is definitely a bad choice.

Automatic date columns in Power Pivot for Excel

Power Pivot for Excel also has a feature to handle the automatic creation of data structures, making it easier to browse dates. However, it uses a different technique that is even worse than that of Power BI. In fact, when one uses a date column in a pivot table, Power Pivot automatically creates a set of calculated columns in the same table that contains the date column. Thus, it creates one calculated column for the year, one for the month name, one for the quarter, and one for the month number—required for sorting. In total, it adds four columns to your table.

As a bad practice, it shares all the bad features of Power BI and it adds a new one. In fact, if there are multiple date columns in a single table, then the number of these calculated columns will start to increase. There is no way to use the same set of columns to slice different dates, as is the case with Power BI. Finally, if the date column is in a table with millions of rows—as is often the case—these calculated columns increase the file size and the memory footprint of the model.

This feature can be disabled in the Excel options, as you can see in Figure 8-2.

Date table template in Power Pivot for Excel

Excel offers another feature that works much better than the previous feature. Indeed, since 2017 there is an option in Power Pivot for Excel to create a date table, which can be activated through the Power Pivot window, as shown in Figure 8-3.

In Power Pivot, clicking on New creates a new table in the model with a set of calculated columns that include year, month, and weekday. It is up to the developer to create the correct set of relationships in the model. Also, if needed, one has the option to modify the names and the formulas of the calculated columns, as well as adding new ones.

There is also the option of saving the current table as a new template, which will be used in the future for newly created date tables. Overall, this technique works well. The table generated by Power Pivot is a regular date table that fulfills all the requirements of a good date table. This, in conjunction with the fact that Power Pivot for Excel does not support calculated tables, makes the feature useful.

Using CALENDAR and CALENDARAUTO

If you do not have a date table in your data source, you can create the date table by using either CALENDAR or CALENDARAUTO. These functions return a table of one column, of DateTime data type. CALENDAR requires you to provide the upper and lower boundaries of the set of dates. CALENDAR-AUTO scans all the date columns across the entire data model, finds the minimum and maximum years referenced, and finally generates the set of dates between these years.

For example, a simple calendar table containing all the dates in the Sales table can be created using the following code:

Click here to view code image

Date =
CALENDAR (
    DATE ( YEAR ( MIN ( Sales[Order Date] ) ), 1, 1 ),
    DATE ( YEAR ( MAX ( Sales[Order Date] ) ), 12, 31 )
)

In order to force all dates from the first of January up to the end of December, the code only extracts the minimum and maximum years, forcing day and month to be the first and last of the year. A similar result can be obtained by using the simpler CALENDARAUTO:

Date = CALENDARAUTO ( )

CALENDARAUTO scans all the date columns, except for calculated columns. For example, if one uses CALENDARAUTO to create a Date table in a model that contains sales between 2007 and 2011 and has an AvailableForSaleDate column in the Product table starting in 2004, the result is the set of all the days between January 1, 2004, and December 31, 2011. However, if the data model contains other date columns, they affect the date range considered by CALENDARAUTO. Storing dates that are not useful to slice and dice is very common. For example, if among the many dates a model also contains the customers’ birthdates, then the result of CALENDARAUTO starts from the oldest year of birth of any customer. This produces a large date table, which in turn negatively affects performance.

CALENDARAUTO accepts an optional parameter that represents the final month number of a fiscal year. If provided, CALENDARAUTO generates dates from the first day of the following month to the last day of the month indicated as an argument. This is useful when you have a fiscal year that ends in a month other than December. For example, the following expression generates a Date table for fiscal years starting on July 1 and ending on June 30:

Date = CALENDARAUTO ( 6 )

CALENDARAUTO is slightly easier to use than CALENDAR because it automatically determines the boundaries of the set of dates. However, it might extend this set by considering unwanted columns. One can obtain the best of both worlds by restricting the result of CALENDARAUTO to only the desired set of dates, as follows:

Click here to view code image

Date =
VAR MinYear = YEAR ( MIN ( Sales[Order Date] ) )
VAR MaxYear = YEAR ( MAX ( Sales[Order Date] ) )
RETURN
FILTER (
    CALENDARAUTO ( ),
    YEAR ( [Date] ) >= MinYear &&
    YEAR ( [Date] ) <= MaxYear
)

The resulting table only contains the useful dates. Finding the first and last day of the year is not that important because CALENDARAUTO handles this internally.

Once the developer has obtained the correct list of dates, they still must create additional columns using DAX expressions. Following is a list of commonly used expressions for this scope, with an example of their results in Figure 8-4:

Click here to view code image

Date =
VAR MinYear = YEAR ( MIN ( Sales[Order Date] ) )
VAR MaxYear = YEAR ( MAX ( Sales[Order Date] ) )
RETURN
ADDCOLUMNS (
     FILTER (
         CALENDARAUTO ( ),
         YEAR ( [Date] ) >= MinYear &&
         YEAR ( [Date] ) <= MaxYear
     ),
     "Year", YEAR ( [Date] ),
     "Quarter Number", INT ( FORMAT ( [Date], "q" ) ),
     "Quarter", "Q" & INT ( FORMAT ( [Date], "q" ) ),
     "Month Number", MONTH ( [Date] ),
     "Month", FORMAT ( [Date], "mmmm" ),
     "Week Day Number", WEEKDAY ( [Date] ),
     "Week Day", FORMAT ( [Date], "dddd" ),
     "Year Month Number", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
     "Year Month", FORMAT ( [Date], "mmmm" ) & " " & YEAR ( [Date] ),
     "Year Quarter Number", YEAR ( [Date] ) * 100 + INT ( FORMAT ( [Date], "q" ) ),
     "Year Quarter", "Q" & FORMAT ( [Date], "q" ) & "-" & YEAR ( [Date] )
)

Instead of using a single ADDCOLUMNS function, one could achieve the same result by creating several calculated columns through the user interface. The main advantage of using ADDCOLUMNS is the ability to reuse the same DAX expression to create a date table in other projects.

Working with multiple dates

When there are multiple date columns in the model, you should consider two design options: creating multiple relationships to the same date table or creating multiple date tables. Choosing between the two options is an important decision because it affects the required DAX code and also the kind of analysis that is possible later on.

Consider a Sales table with the following three dates for every sales transaction:

• Order Date: the date when an order was received.

• Due Date: the date when the order is expected to be delivered.

• Delivery Date: the actual delivery date.

The developer can relate the three dates to the same date table, knowing that only one of the three relationships can be active. Or, they can create three date tables in order to be able to slice by any of the three freely. Besides, it is likely that other tables contain other dates. For example, a Purchase table might contain other dates about the purchase process, a Budget table contains other dates in turn, and so on. In the end, every data model typically contains several dates, and one needs to understand the best way to handle all these dates.

In the next sections, we show two design options to handle this scenario and how this affects the DAX code.

Handling multiple relationships to the Date table

One can create multiple relationships between two tables. Nevertheless, only one relationship can be active. The other relationships need to be kept inactive. Inactive relationships can be activated in CALCULATE through the USERELATIONSHIP modifier introduced in Chapter 5, “Understanding CALCULATE and CALCULATETABLE.”

For example, consider the data model shown in Figure 8-5. There are two different relationships between Sales and Date, but only one can be active. In the example, the active relationship is the one between Sales[Order Date] and Date[Date].

You can create two measures for the sales amount based on a different relationship to the Date table:

Click here to view code image

Ordered Amount :=
SUMX ( Sales, Sales[Net Price] * Sales[Quantity] )

Delivered Amount :=
CALCULATE (
    SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ),
    USERELATIONSHIP ( Sales[Delivery Date], 'Date'[Date] )
)

The first measure, Ordered Amount, uses the active relationship between Sales and Date, based on Sales[Order Date]. The second measure, Delivered Amount, executes the same DAX expression using the relationship based on Sales[Delivery Date]. USERELATIONSHIP changes the active relationship between Sales and Date in the filter context defined by CALCULATE. You can see in Figure 8-6 an example of a report using these measures.

Using multiple relationships with a single date table increases the number of measures in the data model. Generally, one only defines the measures that are meaningful with certain dates. If you do not want to handle a large number of measures, or if you want complete freedom of using any measure with any date, then you might consider implementing calculation groups as explained in the following chapter.

Handling multiple date tables

Instead of duplicating every measure, an alternative approach is to create different date tables—one for each date in the model—so that every measure aggregates data according to the date selected in the report. From a maintenance point of view, this might seem like a better solution because it lowers the number of measures, and it allows for the selecting of sales that intersect between two months, but it produces a model that is harder to use. For example, one can easily produce a report with the total number of orders received in January and delivered in February of the same year—but it is harder to show in the same chart the amounts ordered and delivered by month.

This approach is also known as the role-playing dimension approach. The date table is a dimension that you duplicate once for each relationship—that is, once for each of its roles. These two options (using inactive relationships and duplicating the date table) are complementary to each other.

To create a Delivery Date table and an Order Date table, you add the same table twice in the data model. You must at least modify the table name when doing so. You can see in Figure 8-7 the data model containing two different date tables related to Sales.

Figure 8-8 shows an example of a matrix using multiple date tables. Such a report cannot be created using multiple relationships with a single Date table. You can see that renaming column names and content is important to produce a readable result. In order to avoid confusion between order and delivery dates, we used CY as a prefix for order years and DY as a prefix for delivery years.

Using multiple date tables, the same measure displays different results depending on the columns used to slice and dice. However, it would be wrong to choose multiple date tables just to reduce the number of measures because this makes it impossible to create a report with the same measures grouped by two dates. For example, consider a single line chart showing Sales Amount by Order Date and Delivery Date. One needs a single Date table in the date axis of the chart, and this would be extremely complex to achieve with the multiple date tables pattern.

If your first priority is to reduce the number of measures in a model, enabling the user to browse any measure by any date, you should consider using the calculation groups described in Chapter 9, “Calculation groups,” implementing a single date table in the model. The main scenario where multiple date tables are useful is to intersect the same measure by different dates in the same visualization, as demonstrated in Figure 8-8. In most other scenarios, a single date table with multiple relationships is a better choice.

Using Mark as Date Table

Applying a filter on the date column of a calendar table works fine if the date column also defines the relationship. However, one might have a relationship based on another column. Many existing date tables use an integer column—typically in the format YYYYMMDD—to create the relationship with other tables.

In order to demonstrate the behavior, we created the DateKey column in both the Date and Sales tables. We then linked the two using the DateKey column instead of the date column. The resulting model is visible in Figure 8-11.

Using the same code that worked in previous examples to compute the YTD in Figure 8-11 would result in an incorrect calculation. You see this in Figure 8-12.

As you can see, now the report shows the same value for Sales Amount and for Sales Amount YTD. Indeed, since the relationship is no longer based on a DateTime column, DAX does not add the automatic ALL function to the date table. As such, the filter with the date is intersecting with the previous filter, vanishing the effect of the measure.

In such cases there are two possible solutions: one is to manually add ALL to all the time intelligence calculations. This solution is somewhat cumbersome because it requires the DAX coder to always remember to add ALL to all of the calculations. The other possible solution is much more convenient: mark the Date table as a date table.

If the date table is marked as such, then DAX will automatically add ALL to the table even if the relationship was not based on a date column. Be mindful that once the table is marked as a date table, the automatic ALL on the table is always added whenever one modifies the filter context on the date column. There are scenarios where this effect is undesirable, and in such cases, one would need to write complex code to build the correct filter. We cover this later in this chapter.

Using year-to-date, quarter-to-date, and month-to-date

The calculations of year-to-date (YTD), quarter-to-date (QTD), and month-to-date (MTD) are all very similar. Month-to-date is meaningful only when you are looking at data at the day level, whereas year-to-date and quarter-to-date calculations are often used to look at data at the month level.

You can calculate the year-to-date value of sales for each month by modifying the filter context on dates for a range that starts on January 1 and ends on the month corresponding to the calculated cell. You see this in the following DAX formula:

Click here to view code image

Sales Amount YTD :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( 'Date'[Date] )
)

DATESYTD is a function that returns a table with all the dates from the beginning of the year until the last date included in the current filter context. This table is used as a filter argument in CALCULATE to set the new filter for the Sales Amount calculation. Similar to DATESYTD, there are another two functions that return the month-to-date (DATESMTD) and quarter-to-date (DATESQTD) sets. For example, you can see measures based on DATESYTD and DATESQTD in Figure 8-13.

This approach requires the use of CALCULATE. DAX also offers a set of functions to simplify the syntax of to-date calculations: TOTALYTD, TOTALQTD, and TOTALMTD. In the following code, you can see the year-to-date calculation expressed using TOTALYTD:

YTD Sales :=
TOTALYTD (
    [Sales Amount],
    'Date'[Date]
)

The syntax is somewhat different, as TOTALYTD requires the expression to aggregate as its first parameter and the date column as its second parameter. Nevertheless, the behavior is identical to the original measure. The name TOTALYTD hides the underlying CALCULATE function, which is a good reason to limit its use. In fact, whenever CALCULATE is present in the code, making it evident is always a good practice—for example, for the context transition it implies.

Similar to year-to-date, you can also define quarter-to-date and month-to-date with built-in functions, as in these measures:

Click here to view code image

QTD Sales := TOTALQTD ( [Sales Amount], 'Date'[Date] )
QTD Sales := CALCULATE ( [Sales Amount], DATESQTD ( 'Date'[Date] ) )
MTD Sales := TOTALMTD ( [Sales Amount], 'Date'[Date] )
MTD Sales := CALCULATE ( [Sales Amount], DATESMTD ( 'Date'[Date] ) )

Calculating a year-to-date measure over a fiscal year that does not end on December 31 requires an optional third parameter that specifies the end day of the fiscal year. For example, both the following measures calculate the fiscal year-to-date for Sales:

Click here to view code image

Fiscal YTD Sales := TOTALYTD ( [Sales Amount], 'Date'[Date], "06-30" )
Fiscal YTD Sales := CALCULATE ( [Sales Amount], DATESYTD ( 'Date'Date], "06-30" ) )

The last parameter corresponds to June 30—that is, the end of the fiscal year. There are several time intelligence functions that have a last, optional year-end date parameter for this purpose: STARTOFYEAR, ENDOFYEAR, PREVIOUSYEAR, NEXTYEAR, DATESYTD, TOTALYTD, OPENINGBALANCEYEAR, and CLOSINGBALANCEYEAR.

Fiscal   YTD Sales := TOTALYTD ( [Sales Amount], 'Date'[Date], "30-06"  )
Fiscal  YTD Sales := CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date], "30-06" ) )
Fiscal YTD Sales := CALCULATE ( [Sales Amount], DATESYTD ('Date'[Date],"2018-06-30" ) )

Computing time periods from prior periods

Several calculations are required to get a value from the same period in the prior year (PY). This can be useful for making comparisons of trends during a time period this year to the same time period last year. In that case SAMEPERIODLASTYEAR comes in handy:

Click here to view code image

PY Sales := CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

SAMEPERIODLASTYEAR returns a set of dates shifted one year back in time. SAMEPERIODLASTYEAR is a specialized version of the more generic DATEADD function, which accepts the number and type of period to shift. The types of periods supported are YEAR, QUARTER, MONTH, and DAY. For example, you can define the same PY Sales measure using this equivalent expression, which uses DATEADD to shift the current filter context one year back in time:

Click here to view code image

PY Sales := CALCULATE( [Sales Amount], DATEADD ( 'Date'[Date], -1, YEAR ) )

DATEADD is more powerful than SAMEPERIODLASTYEAR because, in a similar way, DATEADD can compute the value from a previous quarter (PQ), month (PM), or day (PD):

Click here to view code image

PQ Sales := CALCULATE ( [Sales Amount], DATEADD ( 'Date'[Date], -1, QUARTER ) )
PM Sales := CALCULATE ( [Sales Amount], DATEADD ( 'Date'[Date], -1, MONTH ) )
PD Sales := CALCULATE ( [Sales Amount], DATEADD ( 'Date'[Date], -1, DAY ) )

In Figure 8-14 you can see the result of some of these measures.

Another useful function is PARALLELPERIOD, which is similar to DATEADD, but returns the full period specified in the third parameter instead of the partial period returned by DATEADD. Thus, although a single month is selected in the current filter context, the following measure using PARALLEPERIOD calculates the amount of sales for the whole previous year:

Click here to view code image

PY Total Sales :=
CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) )

In a similar way, using different parameters, one can obtain different periods:

Click here to view code image

PQ Total Sales :=
CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, QUARTER ) )

In Figure 8-15 you can see PARALLELPERIOD used to compute the previous year and quarter.

There are functions similar but not identical to PARALLELPERIOD, which are PREVIOUSYEAR, PREVIOUSQUARTER, PREVIOUSMONTH, PREVIOUSDAY, NEXTYEAR, NEXTQUARTER, NEXTMONTH, and NEXTDAY. These functions behave like PARALLELPERIOD when the selection has a single element selected corresponding to the function name—year, quarter, month, and day. If multiple periods are selected, then PARALLELPERIOD returns a shifted result of all of them. On the other hand, the specific functions (year, quarter, month, and day, respectively) return a single element that is contiguous to the selected period regardless of length. For example, the following code returns March, April, and May 2008 in case the second quarter of 2008 (April, May, and June) is selected:

Click here to view code image

PM Total Sales :=
CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, MONTH ) )

Conversely, the following code only returns March 2008 in case the second quarter of 2008 (April, May, and June) is selected.

Click here to view code image

Last PM Sales :=
CALCULATE ( [Sales Amount], PREVIOUSMONTH( 'Date'[Date] ) )

The difference between the two measures is visible in Figure 8-16. The Last PM Sales measure returns the value of December 2007 for both 2008 and Q1 2008, whereas PM Total Sales always returns the value for the number of months of the selection—three for a quarter and twelve for a year. This occurs even though the initial selection is shifted back one month.

Mixing time intelligence functions

One useful feature of time intelligence functions is the capability of composing more complex formulas by using time intelligence functions together. The first parameter of most time intelligence functions is the date column in the date table. However, this is just syntax sugar for the complete syntax. In fact, the full syntax of time intelligence functions requires a table as its first parameter, as you can see in the following two equivalent versions of the same measure. When used, the date column referenced is translated into a table with the unique values active in the filter context after a context transition, if a row context exists:

Click here to view code image

PY Sales :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( 'Date'[Date] )
)

-- is equivalent to

PY Sales :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ) )
)

Time intelligence functions accept a table as their first parameter, and they act as time shifters. These functions take the content of the table, and they shift it back and forth over time by any number of years, quarters, months, or days. Because time intelligence functions accept a table, any table expression can be used in place of the table—including another time intelligence function. This makes it possible to combine multiple time intelligence functions, by cascading their results one into the other.

For example, the following code compares the year-to-date with the corresponding value in the previous year. It does so by combining SAMEPERIODLASTYEAR and DATESYTD. It is interesting to note that exchanging the order of the function calls does not change the result:

Click here to view code image

PY YTD Sales :=
CALCULATE (
    [Sales Amount],
    SAMEPERIODLASTYEAR ( DATESYTD ( 'Date'[Date] ) )
)

-- is equivalent to

PY YTD Sales :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)

It is also possible to use CALCULATE to move the current filter context to a different time period and then invoke a function that, in turn, analyzes the filter context and moves it to a different time period. The following two definitions of PY YTD Sales are equivalent to the previous two; YTD Sales and PY Sales measures are defined earlier in this chapter:

Click here to view code image

PY YTD Sales :=
CALCULATE (
    [YTD Sales],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)
-- is equivalent to

PY YTD Sales :=
CALCULATE (
    [PY Sales],
    DATESYTD ( 'Date'[Date] )
)

You can see the results of PY YTD Sales in Figure 8-17. The values of YTD Sales are reported for PY YTD Sales, shifted one year ahead.

All the examples seen in this section can operate at the year, quarter, month, and day levels, but not at the week level. Time intelligence functions are not available for week-based calculations because there are too many variations of years/quarters/months based on weeks. For this reason, you must implement DAX expressions to handle week-based calculations. You can find more details and an example of this approach in the “Working with custom calendars” section, later in this chapter.

Computing a difference over previous periods

A common operation is calculating the difference between a measure and its value in the prior year. You can express that difference as an absolute value or as a percentage. You have already seen how to obtain the value for the prior year with the PY Sales measure:

Click here to view code image

PY Sales := CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

For Sales Amount, the absolute difference over the previous year (year-over-year or YOY) is a simple subtraction. However, you need to add a failsafe if you want to only show the difference when both values are available. In that case, variables are important to avoid calculating the same measure twice. You can define a YOY Sales measure with the following expression:

Click here to view code image

YOY Sales :=
VAR CySales = [Sales Amount]
VAR PySales = [PY Sales]
VAR YoySales =
    IF (
        NOT ISBLANK ( CySales ) && NOT ISBLANK ( PySales ),
        CySales - PySales
    )
RETURN
    YoySales

The equivalent calculation for comparing the year-to-date measure with a corresponding value in the prior year is a simple subtraction of two measures, YTD Sales and PY YTD Sales. You learned those in the previous section:

Click here to view code image

YTD Sales := TOTALYTD ( [Sales Amount], 'Date'[Date] )

PY YTD Sales :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)

YOY YTD Sales :=
VAR CyYtdSales = [YTD Sales]
VAR PyYtdSales = [PY YTD Sales]
VAR YoyYtdSales =
    IF (
        NOT ISBLANK ( CyYtdSales ) && NOT ISBLANK ( PyYtdSales ),
        CyYtdSales - PyYtdSales
    )
RETURN
    YoyYtdSales

Often, the year-over-year difference is better expressed as a percentage in a report. You can define this calculation by dividing YOY Sales by PY Sales; this way, the difference uses the previous year value as a reference for the percentage difference (100 percent corresponds to a value that is doubled in one year). In the following expressions that define the YOY Sales% measure, the DIVIDE function avoids a divide-by-zero error if there is no corresponding data in the prior year:

Click here to view code image

YOY Sales% := DIVIDE ( [YOY Sales], [PY Sales] )

A similar calculation displays the percentage difference of a year-over-year comparison for the year-to-date aggregation. The following definition of YOY YTD Sales% implements this calculation:

Click here to view code image

YOY YTD Sales% := DIVIDE ( [YOY YTD Sales], [PY YTD Sales] )

In Figure 8-18, you can see the results of these measures in a report.

Computing a moving annual total

Another common calculation that eliminates seasonal changes in sales is the moving annual total (MAT), which considers the sales aggregation over the past 12 months. You learned a technique to compute a moving average in Chapter 7, “Working with iterators and with CALCULATE.” Here we want to describe a formula to compute a similar average by using time intelligence functions.

For example, summing the range of dates from April 2007 to March 2008 calculates the value of MAT Sales for March 2008. The easiest approach is to use the DATESINPERIOD function. DATESINPERIOD returns all the dates included within a period that can be a number of years, quarters, months, or days.

Click here to view code image

MAT Sales :=
CALCULATE (                           -- Compute the sales amount in a new filter
    [Sales Amount],                   -- context modified by the next argument.
    DATESINPERIOD (                   -- Returns a table containing
        'Date'[Date],                 -- Date[Date] values,
        MAX ( 'Date'[Date] ),         -- starting from the last visible date
       -1,                            -- and going back 1
       YEAR                           -- year.
    )
)

Using DATESINPERIOD is usually the best option for the moving annual total calculation. For educational purposes, it is useful to see other techniques to obtain the same filter. Consider this alternative MAT Sales definition, which calculates the moving annual total for sales:

Click here to view code image

MAT Sales :=
CALCULATE (
    [Sales Amount],
    DATESBETWEEN (
        'Date'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) ),
        LASTDATE ( 'Date'[Date] )
    )
)

The implementation of this measure requires some attention. The formula uses the DATESBETWEEN function, which returns the dates from a column included between two specified dates. Because DATESBETWEEN works at the day level, even if the report is querying data at the month level, the code must calculate the first day and the last day of the required interval. A way to obtain the last day is by using the LASTDATE function. LASTDATE is like MAX, but instead of returning a value, it returns a table. Being a table, it can be used as a parameter to other time intelligence functions. Starting from that date, the first day of the interval is computed by requesting the following day (by calling NEXTDAY) of the corresponding date one year before (by using SAMEPERIODLASTYEAR).

One problem with moving annual totals is that they compute the aggregated value—the sum. Dividing this value by the number of months included in the period averages it over the time frame. This gives you a moving annual average (MAA):

Click here to view code image

MAA Sales :=
CALCULATE (
    DIVIDE ( [Sales Amount], DISTINCTCOUNT ( 'Date'[Year Month] ) ),
    DATESINPERIOD (
        'Date'[Date],
        MAX ( 'Date'[Date] ),
       -1,
       YEAR
    )
)

As you have seen, using time intelligence functions results in powerful measures. In Figure 8-19, you can see a report that includes the moving annual total and average calculations.

Using the right call order for nested time intelligence functions

When nesting time intelligence functions, it is important to pay attention to the order used in the nesting. In the previous example, we used the following DAX expression to retrieve the first day of the moving annual total:

Click here to view code image

NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) )

You would obtain the same behavior by inverting the call order between NEXTDAY and SAMEPERIODLASTYEAR, as in the following code:

Click here to view code image

SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( 'Date'[Date] ) ) )

The result is almost always the same, but this order of evaluation presents a risk of producing incorrect results at the end of the period. In fact, authoring the MAT code using this order would result in this version, which is wrong:

Click here to view code image

MAT Sales Wrong :=
CALCULATE (
    [Sales Amount],
    DATESBETWEEN (
        'Date'[Date],
        SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( 'Date'[Date] ) ) ),
        LASTDATE ( 'Date'[Date] )
    )
)

This version of the formula computes the wrong result at the upper boundary of the date range. You can see this happening in a report like the one in Figure 8-20.

The measure computes the correct value up to December 30, 2009. Then, on December 31 the result is surprisingly high. The reason for this is that on December 31, 2009 NEXTDAY should return a table containing January 1, 2010. Unfortunately, the date table does not contain a row with January 1, 2010; thus, NEXTDAY cannot build its result. Consequently, not being able to return a valid result, NEXTDAY returns an empty table. A similar behavior happens with the following function: SAMEPERIODLASTYEAR. It receives an empty table, and as the result, it returns an empty table too. Because DATESBETWEEN requires a scalar value, the empty result of SAMEPERIODLASTYEAR is considered as a blank value. Blank—as a DateTime value—equals zero, which is December 30, 1899. Thus, on December 31, 2009, DATESBETWEEN returns the whole set of dates in the Date table; indeed, the blank as a starting date defines no boundaries for the initial date, and this results in an incorrect result.

The solution is straightforward. It simply involves using the correct order of evaluation. If SAMEPERIODLASTYEAR is the first function called, then on December 31, 2009, it will return a valid date, which is December 31, 2008. Then, NEXTDAY returns January 1, 2009, that this time does exist in the Date table.

In general, all time intelligence functions return sets of existing dates. If a date does not belong to the Date table, then these functions return an empty table that corresponds to a blank scalar value. In some scenarios this behavior might produce unexpected results, as explained in this section. For the specific example of the moving annual total, using DATESINPERIOD is simpler and safer, but this concept is important in case time intelligence functions are combined for other custom calculations.

Using LASTDATE and LASTNONBLANK

DAX offers several functions to handle semi-additive calculations. However, writing the correct code to handle semi-additive calculations is not just a matter of finding the correct function to use. Many subtle details might break a calculation if the author is not paying attention. In this section, we demonstrate different versions of the same code, which will or will not work depending on the data. The purpose of showing “wrong” solutions is educational because the “right” solution depends on the data present in the data model. Also, the solution of more complex scenarios requires some step-by-step reasoning.

The first function we describe is LASTDATE. We used the LASTDATE function earlier, when describing how to compute the moving annual total. LASTDATE returns a table only containing one row, which represents the last date visible in the current filter context. When used as a filter argument of CALCULATE, LASTDATE overrides the filter context on the date table so that only the last day of the selected period remains visible. The following code computes the last balance by using LASTDATE to overwrite the filter context on Date:

Click here to view code image

LastBalance :=
CALCULATE (
    SUM ( Balances[Balance] ),
    LASTDATE ( 'Date'[Date] )
)

LASTDATE is simple to use; unfortunately, LASTDATE is not the correct solution for many semi-additive calculations. In fact, LASTDATE scans the date table always returning the last date in the date table. For example, at the month level it always returns the last day of the month, and at the quarter level it returns the last date of the quarter. If the data is not available on the specific date returned by LASTDATE, the result of the calculation is blank. You see this in Figure 8-24 where the total of Q3 and the grand total are not visible. Because the total of Q3 is empty, the report does not even show Q3, resulting in a confusing result.

If, instead of using the month to slice data at the lowest level, we use the date, then the problem of LASTDATE becomes even more evident, as you can see in Figure 8-25. The Q3 row now is visible, even though its result is still blank.

If there are values on dates prior to the last day of the Date table, and that last day has no data available, then a better solution is to use the LASTNONBLANK function. LASTNONBLANK is an iterator that scans a table and returns the last value of the table for which the second parameter does not evaluate to BLANK. In our example, we use LASTNONBLANK to scan the Date table searching for the last date for which there are rows in the Balances table:

Click here to view code image

LastBalanceNonBlank :=
CALCULATE (
    SUM ( Balances[Balance] ),
    LASTNONBLANK (
        'Date'[Date],
        COUNTROWS ( RELATEDTABLE ( Balances ) )
    )
)

When used at the month level, LASTNONBLANK iterates over each date in the month, and for each date it checks whether the related table with the balances is empty. The innermost RELATEDTABLE function is executed in the row context of the LASTNONBLANK iterator, so that RELATEDTABLE only returns the balances of the given date. If there is no data, then RELATEDTABLE returns an empty table and COUNTROWS returns a blank. At the end of the iteration, LASTNONBLANK returns the last date that computed a nonblank result.

If all customer balances are gathered on the same date, then LASTNONBLANK solves the problem. In our example, we have different dates for different customers within the same month and this creates another issue. As we anticipated at the beginning of this section, with semi-additive calculations the devil is in the details. With our sample data LASTNONBLANK works much better than LASTDATE because it actively searches for the last date. However, it fails in computing correct totals, as you can see in Figure 8-26.

The result for each individual customer looks correct. Indeed, the last known balance for Katie Jordan is 2,531.00, which the formula correctly reports as her total. The same behavior produces correct results for Luis Bonifaz and Maurizio Macagno. Nevertheless, the grand total seems wrong. Indeed, the grand total is 1,950.00, which is the value of Maurizio Macagno only. It is confusing for a report to show a total composed in theory of three values (2,531.00, 2,205.00, 1,950.00) that only sums up the last value.

The reason is not hard to explain. When the filter context filters Katie Jordan, the last date with some values is July 15. When the filter context filters Maurizio Macagno, the last date becomes July 18. Nevertheless, when the filter context no longer filters the customer name, then the last date is Maurizio Macagno’s, which is July 18. Neither Katie Jordan nor Luis Bonifaz have any data on July 18. Therefore, for the month of July the formula only reports the value of Maurizio Macagno.

As it often happens, there is nothing wrong with the behavior of DAX. The problem is that our code is not complete yet because it does not consider the fact that different customers might have different last dates in our data model.

Depending on the requirements, the formula can be corrected in different ways. Indeed, one needs to define exactly what to show at the total level. Given the fact that there is some data on July 18, the idea is to either

• Consider July 18 the last date to use for all the customers, regardless of their individual last date. Therefore, the customers not reported at a certain date have a zero balance at that date.

• Consider each customer’s own last date, then aggregate the grand total using as the last date, the last date of each customer. Thus, the balance account of a customer is always the last balance available for that customer.

Both these definitions are correct, and it all depends on the requirements of the report. Because both are interesting to learn, we demonstrate how to write the code for both. The easier of the two is considering the last date for which there is some data regardless of the customer. The correct formula only requires changing the way LASTNONBLANK computes its result:

Click here to view code image

LastBalanceAllCustomers :=
VAR LastDateAllCustomers =
    CALCULATETABLE (
        LASTNONBLANK (
            'Date'[Date],
            COUNTROWS ( RELATEDTABLE ( Balances ) )
        ),
        ALL ( Balances[Name] )
    )
VAR Result =
    CALCULATE (
        SUM( Balances[Balance] ),
        LastDateAllCustomers
    )
RETURN
    Result

In this code we used CALCULATETABLE to remove the filter from the customer name during the evaluation of LASTNONBLANK. In this case, at the grand total LASTNONBLANK always returns July 18 regardless of the customer in the filter context. As a result, now the grand total adds up correctly, and the end balance of Katie Jordan and Luis Bonifaz is blank, as you can see in Figure 8-27.

The second option requires more complex reasoning. When using a different date for each customer, the grand total cannot be computed by simply using the filter context at the grand total. The formula needs to compute the subtotal of each customer and then aggregate the results. This is one of the scenarios where iterators are a simple and effective solution. Indeed, the following measure uses an outer SUMX to produce the total by summing the individual values of each customer:

Click here to view code image

LastBalanceIndividualCustomer :=
SUMX (
    VALUES ( Balances[Name] ),
    CALCULATE (
        SUM ( Balances[Balance] ),
        LASTNONBLANK (
            'Date'[Date],
            COUNTROWS ( RELATEDTABLE ( Balances ) )
        )
    )
)

The result of this latter measure computes for each customer the value on their own last date. It then aggregates the grand total by summing individual values. You see the result in Figure 8-28.

As you have learned, the complexity of semi-additive calculations is not in the code, but rather in the definition of its desired behavior. Once the behavior is clear, the choice between one pattern and the other is simple.

In this section we showed the most commonly used LASTDATE and LASTNONBLANK functions. There are two similar functions available to obtain the first date instead of the last date within a time period. These functions are FIRSTDATE and FIRSTNONBLANK. Moreover, there are further functions whose goal is to simplify calculations like the one demonstrated so far. We discuss them in the next section.

Working with opening and closing balances

DAX offers many functions like LASTDATE that simplify calculations retrieving the value of a measure at the opening or closing date of a time period. Although useful, these additional functions suffer from the same limitations as LASTDATE. That is, they work well if and only if the dataset contains values for all the dates.

These functions are STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH, and the corresponding closing functions: ENDOFYEAR, ENDOFQUARTER, ENDOFMONTH. Intuitively, STARTOFYEAR always returns January 1 of the currently selected year in the filter context. In a similar way STARTOFQUARTER and STARTOFMONTH return the beginning of the quarter or of the month, respectively.

As an example, we prepared a different dataset that is aimed at resolving a different scenario where semi-additive calculations are useful. The demo file contains the prices of the Microsoft stock between 2013 and 2018. The value is well known at the day level. But what should a report show at an aggregated level—for example, at the quarter level? In this case, the most commonly used value is the last value of the stock price. In other words, stock prices are another example where the semi-additive pattern becomes useful.

A simple implementation of the last value of a stock works well for simple reports. The following formula computes the last value of the Microsoft stock, considering an average of the prices in case there are multiple rows for the same day:

Last Value :=
CALCULATE (
    AVERAGE ( MSFT[Value] ),
    LASTDATE ( 'Date'[Date] )
)

The result is correct when used in a daily chart like Figure 8-29.

However, this nice result is not due to the DAX code working well. The chart looks correct because we used the date level in the x axis, and the client tool—Power BI in this example—works hard to ignore all the empty values in our dataset. This results in a continuous line. But using the same measure in a matrix sliced by year and month would make the gaps in the calculation become much more evident. This shows in Figure 8-30.

Using LASTDATE means you can expect empty values whenever there is no value on the exact last day of the month. That day might be either a weekend or a holiday. The correct version of Last Value is the following:

Click here to view code image

Last Value :=
CALCULATE (
    AVERAGE ( MSFT[Value] ),
    LASTNONBLANK (
        'Date'[Date],
        COUNTROWS ( RELATEDTABLE ( MSFT ) )
    )
)

Being mindful with these functions can prevent unexpected results. For example, imagine computing the Microsoft stock increase from the beginning of the quarter. One option, which again proves to be wrong, is the following code:

Click here to view code image

SOQ :=
CALCULATE (
    AVERAGE ( MSFT[Value] ),
    STARTOFQUARTER ( 'Date'[Date] )
)

SOQ% :=
DIVIDE (
    [Last Value] - [SOQ],
    [SOQ]
)

STARTOFQUARTER returns the date when the current quarter started, regardless the presence of data on that specific date. For example, January 1, which is the start of the first quarter, is also New Year’s Day. Consequently, there is never a price for a stock on that date, and the previous measures produce the result visible in Figure 8-31.

You can note that there are no values for SOQ in the first quarter. Besides, the issue is present for any quarter that starts on a day for which there is no data. To compute the start or the end of a time period, only taking into account dates with data available, the functions to use are FIRSTNONBLANK and LASTNONBLANK mixed with other time intelligence functions like, for example, DATESINPERIOD.

A much better implementation of the SOQ calculation is the following:

Click here to view code image

SOQ :=
VAR FirstDateInQuarter =
    CALCULATETABLE (
        FIRSTNONBLANK (
            'Date'[Date],
            COUNTROWS ( RELATEDTABLE( MSFT ) )
        ),
        PARALLELPERIOD ( 'Date'[Date], 0, QUARTER )
    )
VAR Result =
    CALCULATE (
        AVERAGE ( MSFT[Value] ),
        FirstDateInQuarter
    )
RETURN
    Result

This latter version is much more complex both to author and to understand. However, it works in any scenario by only considering dates with data available. You can see the result of the matrix with the new implementation of SOQ in Figure 8-32.

At the risk of seeming pedantic, it is worth repeating the same concept used when introducing the topic of semi-additive measures. The devil is in the details. DAX offers several functions that work for models with data for all the dates. Unfortunately, not all models contain data for all the dates. In those latter scenarios, it is always extremely important to consider all the possible implications of using these simple functions. One should consider time intelligence functions as building blocks for more complex calculations. Combining different time intelligence functions enables the accurate computing of different time periods, although there is no predefined function solving the problem in a single step.

This is why instead of just showing you a smooth example for each time intelligence function, we preferred walking them through different trial-and-error scenarios. The goal of this section—and of the whole book—is not to just show you how to use functions. The goal is to empower you to think in DAX, to identify which details you should take care of, and to build your own calculations whenever the basic functionalities of the language are not enough for your needs.

In the next section we move one step forward in that same direction, by showing how most time intelligence calculations can be computed without the aid of any time intelligence functions. The goal is not purely educational. When working with custom calendars, such as weekly calendars, time intelligence functions are not useful. You need to be prepared to author some complex DAX code to obtain the desired result.

Understanding periods to date

Earlier, we described the DAX functions that calculate month-to-date, quarter-to-date, and year-to-date: they are DATESMTD, DATESQTD, and DATESYTD. Each of these filter functions is like the result of a FILTER statement that can be written in DAX. For example, consider the following DATESYTD function:

DATESYTD ( 'Date'[Date] )

It corresponds to a filter over the date column using FILTER called by CALCULATETABLE, as in the following code:

Click here to view code image

CALCULATETABLE (
    VAR LastDateInSelection = MAX ( 'Date'[Date] )
    RETURN
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= LastDateInSelection
                && YEAR ( 'Date'[Date] ) = YEAR ( LastDateInSelection )
        )
)

In a similar way, the DATESMTD function:

DATESMTD ( 'Date'[Date] )

corresponds to the following code:

Click here to view code image

CALCULATETABLE (
    VAR LastDateInSelection = MAX ( 'Date'[Date] )
    RETURN
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= LastDateInSelection
                && YEAR ( 'Date'[Date] ) = YEAR ( LastDateInSelection )
                && MONTH ( 'Date'[Date] ) = MONTH ( LastDateInSelection )
        )
)

The DATESQTD function follows the same pattern. All these alternative implementations have a common characteristic: They extract the information about year, month, and quarter from the last day available in the current selection. Then, they use this date to create a suitable filter.

DATESYTD ( 'Date'[Date] )

-- corresponds to

DATESYTD ( CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ) )

DATESYTD ( 'Date'[Date] )

-- corresponds to

VAR LastDateInSelection =
    MAXX ( CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ), [Date] )
RETURN
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= LastDateInSelection
            && YEAR ( 'Date'[Date] ) = YEAR ( LastDateInSelection )
    )

The CALCULATETABLE generated for the column reference used in time intelligence functions is important when you have a row context. Look at the following two calculated columns, both created in the Date table:

Click here to view code image

'Date'[CountDatesYTD] = COUNTROWS ( DATESYTD ( 'Date'[Date] ) )

'Date'[CountFilter] =
COUNTROWS (
    VAR LastDateInSelection =
        MAX ( 'Date'[Date] )
    RETURN
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= LastDateInSelection
                && YEAR ( 'Date'[Date] ) = YEAR ( LastDateInSelection )
        )
)

Though they look similar, they are not. Indeed, you can see the result in Figure 8-33.

CountDatesYTD returns the number of days from the beginning of the year, up to the date in the current row. To achieve this result, DATESYTD should inspect the current filter context and extract the selected period from the filter context. However, being computed in a calculated column, there is no filter context. The behavior of CountFilter is simpler to explain: When CountFilter computes the maximum date, it always retrieves the last date of the entire date table because there are no filters in the filter context. CountDatesYTD behaves differently because DATESYTD performs a context transition being called with a date column reference. Thus, it creates a filter context that only contains the currently iterated date.

If you rewrite DATESYTD and you know that the code will not be executed inside a row context, you can remove the outer CALCULATETABLE, which would otherwise be a useless operation. This is usually the case for a filter argument in a CALCULATE call not called within an iterator—a place where DATESYTD is often used. In these cases, instead of DATESYTD, you can write:

Click here to view code image

VAR LastDateInSelection = MAX ( 'Date'[Date] )
RETURN
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= LastDateInSelection
            && YEAR ( 'Date'[Date] ) = YEAR ( LastDateInSelection )
    )

On the other hand, to retrieve the date from the row context—for example, in a calculated column—it is easier to retrieve the date value of the current row in a variable instead of using MAX:

Click here to view code image

VAR CurrentDate = 'Date'[Date]
RETURN
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= CurrentDate
            && YEAR ( 'Date'[Date] ) = YEAR ( CurrentDate )
    )

DATESYTD allows the specifying of a year-end date, which is useful to compute YTD on fiscal years. For example, for a fiscal year starting on July 1, June 30 needs to be specified in the second argument by using one of the following versions:

Click here to view code image

DATESYTD ( 'Date'[Date], "06-30" )
DATESYTD ( 'Date'[Date], "30-06" )

Regardless of the local culture, let us assume that the programmer has specified the <month> and <day>. The corresponding FILTER of DATESYTD using these placeholders is the following:

Click here to view code image

VAR LastDateInSelection = MAX ( 'Date'[Date] )
RETURN
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] > DATE ( YEAR ( LastDateInSelection ) - 1, <month>, <day> )
            && 'Date'[Date] <= LastDateInSelection
    )

Understanding DATEADD

DATEADD retrieves a set of dates shifted in time by a certain offset. When DATEADD analyzes the current filter context, it includes special handling to detect whether the current selection is one month or a special period, like the beginning or the end of a month. For example, when DATEADD retrieves an entire month shifted back one quarter, it oftentimes returns a different number of days than the current selection. This happens because DATEADD understands that the current selection is a month, and it retrieves a full corresponding month regardless of the number of days.

These special behaviors are expressed in three rules that we describe in this section. These rules make it hard to rewrite DATEADD on a generic date table. The code would be painfully difficult to write and nearly impossible to manage over time. DATEADD only uses the values of the date column, extracting the information needed—such as year, quarter, and month—from the available date value. The same logic would be hard to reproduce in plain DAX. On the other hand, by using additional columns in the Date table, one can author an alternative version of DATEADD. We will elaborate on this technique later in this chapter, in the section about custom calendars.

Consider the following formula:

Click here to view code image

DATEADD ( 'Date'[Date], -1, MONTH )

The closest—but not totally equivalent—DAX formula is the following:

Click here to view code image

VAR OffsetMonth = -1
RETURN TREATAS (
    SELECTCOLUMNS (
        CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ),
        "Date", DATE (
            YEAR ( 'Date'[Date] ),
            MONTH ( 'Date'[Date] ) + OffsetMonth,
            DAY ( 'Date'[Date] )
        )
    ),
    'Date'[Date]
)

The formula also works in January because a value lower than 1 for the month parameter is considered an offset to go into a previous year. However, this implementation only works properly if the destination month has the same number of days as the current month. If you move from February to January, the formula misses two or three days, depending on the year. In a similar way, if you move from March to February, the result might include days in March.

On the other hand, DATEADD does not have a similar problem and returns the entire month with the offset applied, in case an entire month was selected before the offset was applied. In order to achieve this, DATEADD uses three rules:

1. DATEADD only returns days that exist in the date column. If some expected dates are missing, then DATEADD returns only those dates that are not missing in the date column.

2. If a day does not exist in the corresponding month after the shifting operation, then the result of DATEADD includes the last day of the corresponding month.

3. If the selection includes the last two days of a month, then the result of DATEADD includes all the days between the corresponding days in the shifted month and the end of the shifted month.

A few examples are helpful to understand the effects of these behaviors. Consider the following measures: Day count counts the number of selected days; PM Day count counts the number of days shifted back in the previous month; PM Range returns the date range selected by DATEADD.

Click here to view code image

Day count :=
COUNTROWS ( 'Date' )

PM Day count :=
CALCULATE ( [Day count], DATEADD ( 'Date'[Date], -1, MONTH ) )

PM Range :=
CALCULATE (
    VAR MinDate = MIN ( 'Date'[Date] )
    VAR MaxDate = MAX ( 'Date'[Date] )
    VAR Result =
        FORMAT ( MinDate, "MM/DD/YYYY - " ) & FORMAT ( MaxDate, "MM/DD/YYYY" )
    RETURN
        Result,
    DATEADD ( 'Date'[Date], -1, MONTH )
)

• Rule 1 is in effect when the selection is near the boundaries of the range of dates included in the date column. For example, Figure 8-34 shows the PM Day count and PM Range measures returning valid values in February 2007 because dates in January 2007 exist in the date column, whereas the same measures return blanks in January 2007 because dates in December 2006 are not present in the date column.

  

  The main reason why the Date table should include all the days within one year is because of the behavior of DATEADD. Be mindful that several time intelligence functions in DAX internally use DATEADD. Therefore, having a complete date table is of paramount importance for DAX time intelligence functions to behave as expected.

• Rule 2 is relevant because months have different numbers of days. The 31^st day does not exist for all months. If it is selected, it is moved to the last day of the month in the shifted period. For example, in Figure 8-35 the last days of March are all moved to the last day of February because February 29 to 31 do not exist in 2007.

  

  The consequence of this rule is that you might obtain a lower number of days than the initial selection. This is intuitive when the selection of 31 days in March should result in a corresponding selection of 28 or 29 days in February. However, when the selection includes a reduced number of days, the result might not be what is expected. For example, in Figure 8-36 you can see that a selection of 5 days in March 2007 results in only 2 days in February 2007.

  

• Rule 3 generates a special handling type when the last day of a month is included within a range of dates. For example, consider the initial selection of three days from June 29, 2007, to July 1, 2007. The selection only includes three days, but among those is the last day of June, which is June 30. When DATEADD shifts the dates back, it includes the last day of May (May 31). Figure 8-37 shows this behavior and it is worth a deeper look. Indeed, you can note that June 30 is moved to May 30. Only if the selection contains both June 29 and 30 does the result then include the last day of the previous month (May 31). In this case, the number of days in the previous month is greater than the number of days originally selected: 2 days selected in June 2017 return 3 days in the previous month (May 2007).

  

The reason for these rules is to provide an intuitive behavior when a formula operates at the month level. As you can see in Figure 8-38, when you compare the selections at the month level, the result is intuitive and expected. It shows the complete range of days of the previous month.

Understanding the rules described in this section is important to handle side conditions that might happen with partial selections of days in months. For example, consider a filter over weekdays in a report. That filter might not include the last days of a month, which would guarantee that the entire previous month is selected. Moreover, the shift of dates performed by DATEADD only considers the number of days within the month and not the week days. The application of a filter to the date column of the Date table also generates an implicit ALL over the Date table itself, removing any existing filter over other columns of the Date table including weekdays. Thus, a slicer that filters weekdays is not compatible with the use of DATEADD because it does not produce the expected result.

For example, consider the following definition of PM Sales DateAdd displaying the Sales Amount of the previous month, as shown in Figure 8-39:

Click here to view code image

PM Sales DateAdd :=
CALCULATE (
    [Sales Amount],
    DATEADD ( 'Date'[Date], -1, MONTH )
)

PM Sales DateAdd creates a filter of days that does not correspond to the full month. It translates the days of the month selected, including additional days at the end of the month according to Rule 3. This filter overrides and ignores the Day of Week selection for the value of the previous month. The result produces different values, even bigger than Sales Amount as in March and May 2007, for example.

In this case, computing correctly requires a custom calculation like the one implemented in the PM Sales Weekday measure. It applies a filter over the YearMonthNumber column keeping the filter on Day of Week, and removing the filter from all the other columns of the Date table using ALLEXCEPT. The YearMonthNumber calculated column is a sequential number over months and years:

Click here to view code image

Date[YearMonthNumber] =
'Date'[Year] * 12 + 'Date'[Month Number] - 1

PM Sales Weekday :=
VAR CurrentMonths = DISTINCT ( 'Date'[YearMonthNumber] )
VAR PreviousMonths =
    TREATAS (
        SELECTCOLUMNS (
            CurrentMonths,
            "YearMonthNumber", 'Date'[YearMonthNumber] - 1
        ),
        'Date'[YearMonthNumber]
    )
VAR Result =
    CALCULATE (
        [Sales Amount],
        ALLEXCEPT ( 'Date', 'Date'[Week Day] ),
        PreviousMonths
    )
RETURN
    Result

The result is visible in Figure 8-40.

However, this solution works specifically for this report. If the selection of days were made based on other criteria like the first 6 days of the month, then the result produced by PM Sales Weekday would get the entire month, whereas the result produced by PM Sales DateAdd would work in this case. Depending on the columns visible to the user, one might implement different calculations based on the selection made. For example, the following PM Sales measure uses the ISFILTERED function to check whether a filter is active on the Day of Week column. A more detailed explanation of ISFILTERED is included in Chapter 10.

Click here to view code image

PM Sales :=
IF (
    ISFILTERED ( 'Date'[Day of Week] ),
    [PM Sales Weekday],
    [PM Sales DateAdd]
)

Understanding FIRSTDATE, LASTDATE, FIRSTNONBLANK, and LASTNONBLANK

In the “Understanding semi-additive calculations” section earlier in this chapter, you learned two functions that seem alike: LASTDATE and LASTNONBLANK. In fact, these functions exhibit distinctive behaviors, and so do the two companions FIRSTDATE and FIRSTNONBLANK.

FIRSTDATE and LASTDATE only operate on a date column. They return, respectively, the first and the last date in the active filter context, ignoring any data existing in other related tables:

FIRSTDATE ( 'Date'[Date] )
LASTDATE ( 'Date'[Date] )

FIRSTDATE returns the minimum value of the column received in the current filter context, whereas LASTDATE returns the maximum value. FIRSTDATE and LASTDATE behave somewhat like MIN and MAX, with one important difference: FIRSTDATE and LASTDATE return a table and perform a context transition, whereas MIN and MAX return a scalar value without doing any context transition.

For example, consider the following expression:

Click here to view code image

CALCULATE (
    SUM ( Inventory[Quantity] ),
    LASTDATE ( 'Date'[Date] )
)

You can rewrite the formula using MAX instead of LASTDATE, but this would result in unnecessary longer code:

Click here to view code image

CALCULATE (
    SUM ( Inventory[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] = MAX ( 'Date'[Date] )
    )
)

Besides, LASTDATE also performs a context transition. Consequently, the exact equivalent of LASTDATE in plain DAX is as follows:

Click here to view code image

CALCULATE (
    SUM ( Inventory[Quantity] ),
    VAR LastDateInSelection =
        MAXX ( CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ), 'Date'[Date] )
    RETURN
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] = LastDateInSelection
        )
)

The context transition is relevant when you execute FIRSTDATE/LASTDATE in a row context. The best practice is to use FIRSTDATE/LASTDATE when you write a filter expression because a table expression is expected, whereas MIN/MAX functions are better when you are writing a logical expression in a row context that usually requires a scalar value. Indeed, LASTDATE with a column reference implies a context transition that hides the external filter context.

For example, you will favor FIRSTDATE/LASTDATE over MIN/MAX in a filter argument of CALCULATE/CALCULATETABLE functions because the syntax is simpler. However, you should use MIN/MAX when the context transition implied by FIRSTDATE/LASTDATE would modify the result. This is the case of the condition in a FILTER function. The following expression filters the dates for computing a running total:

Click here to view code image

FILTER (
    ALL ( 'Date'[Date] ),
    'Date'[Date] <= MAX ( 'Date'[Date] )
)

MAX is the right function to use. In fact, the result of using LASTDATE instead of MAX would always contain all the dates, regardless of the current selection because of the unwanted context transition. Thus, the following expression returns all dates, no matter what. The reason is that LASTDATE—because of context transition—returns the value of Date[Date] in each row of the FILTER iteration:

Click here to view code image

FILTER (
    ALL ( 'Date'[Date] ),
    'Date'[Date] <= LASTDATE ( 'Date'[Date] ) -- this condition is always true
)

LASTNONBLANK and FIRSTNONBLANK are different from FIRSTDATE and LASTDATE. In fact, LASTNONBLANK and FIRSTNONBLANK are iterators, meaning that they scan a table row by row in a row context and that they return the last (or first) of the values for which the second parameter is not a blank. Usually, the second parameter of these functions is either a measure or an expression including CALCULATE, so to rely on context transition.

To obtain the right value for the last non-blank date for a given measure/table, you use an expression like this:

Click here to view code image

LASTNONBLANK ( 'Date'[Date], CALCULATE ( COUNTROWS ( Inventory ) ) )

It returns the last date (in the current filter context) for which there are rows in the Inventory table. You can also use an equivalent formula:

Click here to view code image

LASTNONBLANK ( 'Date'[Date], COUNTROWS ( RELATEDTABLE ( Inventory ) ) )

That last expression returns the last date (in the current filter context) for which there is a related row in the Inventory table.

It is worth noting that FIRSTNONBLANK/LASTNONBLANK functions accept any data type as their first argument, whereas the FIRSTDATE/LASTDATE functions require a column of DateTime or Date data type. Thus, and though it is not a commonly used practice, FIRSTNONBLANK and LASTNONBLANK can also be used with different tables like customers, products, or any other table.

Using drillthrough with time intelligence

A drillthrough operation is a request for the data source rows corresponding to the filter context used in a certain calculation. Every time you use a time intelligence function, you change the filter context on the Date table. This produces a different result for the measure from the result obtained with the initial filter context. When you use a client that performs a drillthrough action over a report, such as a pivot table in Excel, you could observe a behavior that is not what you might expect. In fact, the drillthrough operation made in MDX does not consider the changes in the filter context defined by the measure itself. Instead, it only considers the filter context defined by the rows, columns, filters, and slicers of the pivot table.

For example, by default the drillthrough on March 2007 always returns the same rows, regardless of the time intelligence function applied in the measure. By using TOTALYTD, one would expect all the days from January to March 2007; by using SAMEPERIODLASTYEAR, one would expect March 2006; and by using LASTDATE, one would only expect the rows for March 31, 2007. Indeed, in the default drillthrough any of these filters always returns all the rows for March 2007. This behavior can be controlled by the Detail Rows property in the Tabular model. At the time of writing (April 2019), the Detail Rows property can be set in an Analysis Services 2017 or Azure Analysis Services data model, but it is not available either in Power BI or in Power Pivot for Excel.

The Detail Rows property must apply the same filter used for the corresponding time intelligence measure. For example, consider the following year-to-date measure:

CALCULATE (
    [Sales Amount],
    DATESYTD ( 'Date'[Date] )
)

Its Detail Rows property should be set to

Click here to view code image

CALCULATETABLE (
    Sales,                       -- This expression also controls the columns returned
    DATESYTD ( 'Date'[Date] )
)

Working with weeks

DAX does not provide any time intelligence functions that handle weeks. The reason is that there are many different standards and techniques to define weeks within a year, and to define the notion of calculation over weeks. Oftentimes a single week crosses the boundaries of years, quarters, and months. You need to write the code to handle your own definition of a week-based calendar. For example, in ISO a week-date system of January 1 and January 2 in 2011 belongs to week 52 of year 2010, and the first week of 2011 starts on January 3.

Although there are different standards, you can learn a generic approach that should work in most cases. The approach involves the creation of additional columns in the Date table to store the relationship between weeks and their month/quarter/year. Changing the association rules will just require changing the content of the Date table, without modifying the DAX code of the measures.

For example, you can extend a Date table to support ISO weeks by using the following calculated columns:

Click here to view code image

'Date'[Calendar Week Number] = WEEKNUM ( 'Date'[Date], 1 )

'Date'[ISO Week Number] = WEEKNUM ( 'Date'[Date], 21 )

'Date'[ISO Year Number] = YEAR ( 'Date'[Date] + ( 3 - WEEKDAY ( 'Date'[Date], 3 ) ) )

'Date'[ISO Week] = "W" & 'Date'[ISO Week Number] & "-" & 'Date'[ISO Year Number]

'Date'[ISO Week Sequential] = INT ( ( 'Date'[Date] - 2 ) / 7 )

'Date'[ISO Year Day Number] =
VAR CurrentIsoYearNumber = 'Date'[ISO Year Number]
VAR CurrentDate = 'Date'[Date]
VAR DateFirstJanuary = DATE ( CurrentIsoYearNumber, 1, 1 )
VAR DayOfFirstJanuary = WEEKDAY ( DateFirstJanuary, 3 )
VAR OffsetStartIsoYear = - DayOfFirstJanuary + ( 7 * ( DayOfFirstJanuary > 3 ) )
VAR StartOfIsoYear = DateFirstJanuary + OffsetStartIsoYear
VAR Result = CurrentDate - StartOfIsoYear
RETURN
    Result

You can see in Figure 8-41 the result of these columns. The ISO Week column will be visible to users, whereas the ISO Week Sequential Number is for internal use only. ISO Year Day Number is the number of days since the beginning of the ISO year. These additional columns make it easy to compare different periods.

Using the new columns, a developer can write year-to-date aggregation by using the ISO Year Number column instead of extracting the year number from the date. This technique is the same as the one you learned in the “Understanding periods to date” section earlier in this chapter. We just added an additional check to make sure that only one ISO Year is selected, prior to invoking the VALUES function:

Click here to view code image

ISO YTD Sales :=
IF (
    HASONEVALUE ( 'Date'[ISO Year Number] ),
    VAR LastDateInSelection = MAX ( 'Date'[Date] )
    VAR YearSelected = VALUES ( 'Date'[ISO Year Number] )
    VAR Result =
        CALCULATE (
            [Sales Amount],
            'Date'[Date] <= LastDateInSelection,
            'Date'[ISO Year Number] = YearSelected,
            ALL ( 'Date' )
        )
    RETURN
        Result
)

Figure 8-42 shows the result of the ISO YTD Sales measure at the beginning of 2008, compared with a standard YTD computed through DATESYTD. The ISO version accurately includes December 31, 2007, which belongs to ISO Year 2008.

The comparison with the prior year should compare the relative weeks of the year with the same weeks in the previous year. Since the dates might be different, it is simpler to use other columns in the date table to implement the comparison logic. The distribution of weeks within each year is regular because each week always has seven days, whereas calendar months have different lengths and cannot benefit from the same assumption. In week-based calendars, you can simplify the calculation by looking in the previous year for the same relative days that were selected in the current filter context.

The following ISO PY Sales measure filters the same selection of days in the previous year. This technique also works when the selection includes complete weeks because the days are selected using the ISO Year Day Number value and not the effective date.

Click here to view code image

ISO PY Sales :=
IF (
    HASONEVALUE ( 'Date'[ISO Year Number] ),
    VAR DatesInSelection = VALUES ( 'Date'[ISO Year Day Number] )
    VAR YearSelected = VALUES ( 'Date'[ISO Year Number] )
    VAR PrevYear = YearSelected - 1
    VAR Result =
        CALCULATE (
            [Sales Amount],
            DatesInSelection,
            'Date'[ISO Year Number] = PrevYear,
            ALL ( 'Date' )
        )
    RETURN
        Result
)

Figure 8-43 shows the result produced by the ISO PY Sales measure. On the right we added the sales amount of 2007, to make it easier to understand the source of ISO PY Sales.

Week-based calendars are simple to manage because of the assumption you can make about the symmetry between days in different years. This is usually not compatible with the calendar month, so if you want to use both hierarchies (months and weeks), you should create different time intelligence calculations for each hierarchy.

Custom year-to-date, quarter-to-date, and month-to-date

Earlier in this chapter, you learned how to rewrite DATESYTD and similar functions in the “Understanding periods to date” section. There, we could still extract date attributes—like the year—from the date column. With ISO calendars, this logic is no longer in the date column. Instead, we created additional columns just for this calculation. In this section we now demonstrate how to replace the logic that extracts information from the date value by using other columns of the Date table.

For example, consider the following YTD Sales measure:

YTD Sales :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( 'Date'[Date] )
)

The corresponding syntax in DAX without time intelligence is the following:

Click here to view code image

YTD Sales :=
VAR LastDateInSelection = MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        [Sales Amount],
        'Date'[Date] <= LastDateInSelection
           && YEAR ( 'Date'[Date] ) = YEAR ( LastDateInSelection )
    )
RETURN
    Result

If you use a custom calendar, you must replace the YEAR function call with an access to the Year column, such as in the following YTD Sales Custom measure:

Click here to view code image

YTD Sales Custom :=
VAR LastDateInSelection = MAX ( 'Date'[Date] )
VAR LastYearInSelection = MAX ( 'Date'[Calendar Year Number] )
VAR Result =
    CALCULATE (
        [Sales Amount],
        'Date'[Date] <= LastDateInSelection,
        'Date'[Calendar Year Number] = LastYearInSelection,
        ALL ( 'Date' )
    )
RETURN
    Result

You can use the same template to implement quarter-to-date and month-to-date calculations. The only difference is the column used instead of Calendar Year Number:

Click here to view code image

QTD Sales Custom :=
VAR LastDateInSelection = MAX ( 'Date'[Date] )
VAR LastYearQuarterInSelection = MAX ( 'Date'[Calendar Year Quarter Number] )
VAR Result =
    CALCULATE (
        [Sales Amount],
        'Date'[Date] <= LastDateInSelection,
        'Date'[Calendar Year Quarter Number] = LastYearQuarterInSelection,
        ALL ( 'Date' )
    )
RETURN
    Result

MTD Sales Custom :=
VAR LastDateInSelection = MAX ( 'Date'[Date] )
VAR LastYearMonthInSelection = MAX ( 'Date'[Calendar Year Month Number] )
VAR Result =
    CALCULATE (
        [Sales Amount],
        'Date'[Date] <= LastDateInSelection,
        'Date'[Calendar Year Month Number] = LastYearMonthInSelection,
        ALL ( 'Date' )
    )
RETURN
    Result

You can use these formulas to implement calculations for both standard calendars (in case you want to improve performance using DirectQuery) and custom calendars (in case the time periods are not standard periods).