Microsoft PowerPivot for Excel 2010: Date Calculations in DAX

10/5/2010

Contents

Closing Balance over Time

In a PivotTable, each cell contains the result of applying an aggregation function to a measure. Whenever that function is SUM, the measure is called an additive measure because SUM is applied over all dimensions. Whenever another function is applied, such as AVERAGE, MIN, or MAX, the measure is called a nonadditive measure because an aggregation function other than SUM is applied over all dimensions. However, it is important to note that both for additive and nonadditive measures, the same aggregation function is always applied over all dimensions, without exception.

Semiadditive Measures

Some measures should behave in a different way. For example, think about the balance for a bank account. If you consider several accounts, you can calculate the total balance for an occupation by summing up all the balances of customers grouped by occupation. However, you cannot sum the same balance twice, and you probably have several balances of the same account that measure it over time. For example, in Figure 7-49, you can see a Balance table in Excel: the same account has a balance value for each date. This type of measure is called a semiadditive measure, because it can be aggregated using SUM over some dimensions but requires a different aggregation algorithm over other dimensions. You can find the following example in the CH07-11-SemiAdditive.xlsx workbook included on the companion DVD.

Figure 7-49 The raw balance account data.

In the case of account balance data, the only dimension that cannot be summed is the Date. With the term dimension Date, we include all the attributes of a Dates table related to the table containing the real measures. The logic that has to be implemented for the Date attributes is to consider only the values belonging to the last date in the evaluated period. In other words, you must implement a logic that can produce the same results that you see in Figure 7-50.

Figure 7-50 The result of applying the LastBalance measure.

The LastBalance measure used in Figure 7-50 calculates the total of a quarter by using just the last month available in that period. For each month, only the last date for that month is considered. So the total of a quarter is calculated using only the last day of that quarter. You can define the LastBalance measure in this way:

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

The definition of the LastBalance measure uses the LASTDATE function to keep just the last date that is active in the current filter context. So only the last date in the selected period is considered in the CALCULATE call.

As usual, you must use a separate Dates table. Remember that the last date in a period is the last date available in the BalanceDate table (mentioned in the preceding formula) and not the last date for which there is raw data. This might have unwanted consequences. If your data does not have values for the last day of a month and the Dates table contains all the days for that month, the LastBalance formula you have used returns no data (a blank value) for that month. Consider the last two months available in the Balances table, as shown in Figure 7-51.

Figure 7-51 The last two months of balance account data.

The Balances table contains a balance for each account and each last day of the month, but for December the last day available is December 15. If the BalanceDate table contains all the days for year 2010, including 31 days for December, the LastBalance measure tries to filter balance data for December 31, which is not available, resulting in a PivotTable like the one shown in Figure 7-52, where the row for December is missing.

Figure 7-52 December and 4th Quarter totals are missing.

A possible solution is to delete rows from the BalanceDate from December 16 through December 31. In this way, the LastBalance measure returns values as previously shown in Figure 7-50. Another option is to use the LASTNONBLANK function, which returns the last date for which a particular expression is not blank. The use of this function is not very intuitive when the Dates column and the expression you want to evaluate manage different tables. First of all, this is a formula for a LastBalanceNonBlank measure that works also with the BalanceDate complete with all the dates through December 31.

LastBalancaNonBlank = CALCULATE( SUM( Balances[Balance] ),
                                 LASTNONBLANK( BalanceDate[Date],
                                               COUNTROWS( RELATEDTABLE(Balances) ) ) )

The preceding formula produces exactly the result you saw in Figure 7-50, without your needing to remove rows from the BalanceDate table.

Using FIRSTNONBLANK and LASTNONBLANK Functions

The LASTNONBLANK function you have just seen has a particular behavior, shared also by FIRSTNONBLANK. The syntax of these functions is the following one:

FIRSTNONBLANK( <column>, <expression> )
LASTNONBLANK( <column>, <expression> )

These functions return the first or last value in <column>, filtered by the current context, wherein the <expression> is not blank. So these functions behave like SUMX or similar functions in this regard. They set a row context for a value of <column> and then evaluate the <expression> by using that row context. If <expression> and <column> manage data of the same table, everything works fine. However, whenever <expression> uses columns of tables other than the one to which <column> belongs, you need to transform a row context into a filter context by using RELATEDTABLE or CALCULATE. This is a very common situation every time you have a separate Dates table, which is the best practice for every date-related calculation.

To get the right value for the last nonblank date for a given measure/table, you have to use something like this:

=LASTNONBLANK( Dates[Date], CALCULATE( COUNT( Balances[Balance] ) ) )

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

=LASTNONBLANK( Dates[Date], COUNTROWS( RELATEDTABLE( Sales ) ) )

This formula returns the last date (in the current filter context) for which there is a related row in the Sales table.

OPENINGBALANCE and CLOSINGBALANCE Functions

DAX provides several functions to get the first and last date of a period (year, quarter, or month) that are useful whenever you need to get that value of a selection that is smaller than the whole period considered. For example, looking at the month level (which may be displayed in rows), you might want to display also the value of the end of the quarter and the end of the year in the same row, as you can see in Figure 7-53. (The examples shown in this section are also available in the CH07-12-ClosingBalance.xlsx workbook included on the companion DVD.)

Figure 7-53 The balance data at end of month, quarter, and year for each month.

The formulas used to calculate ClosingBalanceMonth, ClosingBalanceQuarter, and ClosingBalanceYear measures are the following:

ClosingBalanceMonth = CLOSINGBALANCEMONTH( SUM( Balances[Balance] ), BalanceDate[Date] )
ClosingBalanceQuarter = CLOSINGBALANCEQUARTER( SUM( Balances[Balance] ), BalanceDate[Date] )
ClosingBalanceYear = CLOSINGBALANCEYEAR( SUM( Balances[Balance] ), BalanceDate[Date] )

These formulas use the LASTDATE function internally, but they operate on a set of dates that can extend the current selection in the PivotTable. For example, the CLOSINGBALANCEYEAR function considers the LASTDATE of Balance[Date], which is applied to the last year period of the dates included in the filter context. So for February 2010 (and for any month or quarter of 2010), this date is December 31, 2010. The CLOSINGBALANCEYEAR function behaves like a CALCULATE expression using the ENDOFYEAR function as a filter. As usual, the use of CALCULATE is more generic and flexible, but specific DAX functions like CLOSINGBALANCEYEAR better express the intention of the measure designer. The following are measures equivalent to the ones previously shown using CALCULATE syntax.

ClosingBalanceEOM = CALCULATE( SUM( Balances[Balance] ), ENDOFMONTH( BalanceDate[Date] ) )
ClosingBalanceEOQ = CALCULATE( SUM( Balances[Balance] ), ENDOFQUARTER( BalanceDate[Date] ) )
ClosingBalanceEOY = CALCULATE( SUM( Balances[Balance] ), ENDOFYEAR( BalanceDate[Date] ) )

An important consideration has to be made about dates for which there is available data in your model. You can see this if you drill down to data at the day level in the PivotTable. Before doing that, consider the raw data set we used in this example, shown in Figure 7-54. As you can see, there are more balances for each month. For example, in January there are balances for days 8, 15, 22, and 31.

Figure 7-54 The raw balance data with more balances for each month.

If you browse this data at the day level in the PivotTable by using the same measures as the previous example, you see the results shown in Figure 7-55.

Figure 7-55 Browsing data at the day level displays rows with no balance data.

As you can see, the measures defined to display values at the end of the period suffer an unpleasant side effect: all the dates are visible, even those for which there are no balance data available. If you want to display just the rows corresponding to dates with balance data defined, you have to modify the measures, checking the existence of data in the Balances table, in this way:

ClosingBalanceMonth2
    = IF( COUNTROWS( Balances ) > 0,
          CLOSINGBALANCEMONTH( SUM( Balances[Balance] ), BalanceDate[Date] ),
          BLANK() )

ClosingBalanceQuarter2
    = IF( COUNTROWS( Balances ) > 0,
          CLOSINGBALANCEQUARTER( SUM( Balances[Balance] ), BalanceDate[Date] ),
          BLANK() )

ClosingBalanceYear2
    = IF( COUNTROWS( Balances ) > 0,
          CLOSINGBALANCEYEAR( SUM( Balances[Balance] ), BalanceDate[Date] ),
          BLANK() )

Browsing data using these measures results in a report like the one shown in Figure 7-56.

Figure 7-56 Using measures that display only days for which there is balance data.

By default, the PivotTable in Excel does not display empty rows and columns. For this reason, the days containing no balance date are not shown: all the measures used in the PivotTable return BLANK for those days, removing them from the report.

Updating Balances by Using Transactions

The balance account model you saw in the previous section makes an important assumption: for a given date, either data is not present at all or all the accounts have a balance value for that date. In case an account does not have a balance value for a date that other accounts are measured, that account is considered to have a zero balance for that date. This assumption is good for certain data structures, which are generated by a system that makes a snapshot of the situation (all balance accounts values) on a given date.

However, some scenarios have a different data model in which the previous assumption is not valid. For example, consider this other way to collect data about balance accounts. In the Balances table shown in Figure 7-57, data has been normalized by means of an Accounts table, which can be seen on the right side of the same figure. (The model used in this section is available in the CH07-13-ClosingTransaction.xlsx workbook included on the companion DVD.) Moreover, you can find a balance row for an account only for dates when a transaction made some changes in the account balance.

Figure 7-57 Raw balance account data updated for transactions and not in snapshots.

As you can see, account A001 changes its value on January 1, 12, 20, and 25; account A002 changes on January 1, 14, 21, and 26; and account A003 changes on January 1, 15, 22, and 30. There is no data at the end of month (January 31), and there is no data for all accounts on a given date (for example, January 12 has an account balance only for account A001). So neither LastBalance nor ClosingBalance measures we have seen before can work with this data because their initial assumptions are not valid anymore. We must create a more complex calculation.

The basic idea is that, for each account, you must get the last nonblank date included in the selected period. The calculation for a single account can be made by using the CALCULATE function and by filtering data on the LASTNONBLANK date included in the period between the first date available and the last date in the period. Notice that the date range considered begins even outside the period: you might request the balance for February and there might be no rows in that month, so previous dates also must be considered for the interval. You use a SUMX function to iterate all the available accounts.

SUMX( ALL( Balances[Account] ),
      CALCULATE( SUM( Balances[Balance] ),
                 LASTNONBLANK( DATESBETWEEN( BalanceDate[Date],
                                             BLANK(),
                                             LASTDATE( BalanceDate[Date] ) ),
                               CALCULATE( COUNT( Balances[Balance] ) ) ) ) )

This expression calculates a value for each date in the BalanceDate table. To get the calculation only for dates that have at least one transaction (for any account), you must make a test similar to the one you saw already in the previous section for ClosingBalance measures. Finally, you can define the complete LastBalanceTx measure by using this DAX formula:

LastBalanceTx
  = IF( COUNTX( BalanceDate,
                CALCULATE( COUNT( Balances[Balance] ),
                           ALLEXCEPT( Balances, BalanceDate[Date] ) ) ) > 0,
        SUMX( ALL( Balances[Account] ),
              CALCULATE( SUM( Balances[Balance] ),
                         LASTNONBLANK( DATESBETWEEN( BalanceDate[Date],
                                                     BLANK(),
                                                     LASTDATE( BalanceDate[Date] ) ),
                                       CALCULATE( COUNT( Balances[Balance] ) ) ) ) ),
        BLANK() )

This formula produces the result shown in Figure 7-58, in which you can see the balance updated for each account (one for each column) only for days in which at least one new balance is present in the Balances table.

Figure 7-58 Results of LastBalanceTx measure.

TIP

This scenario requires a particularly complex DAX calculation, which becomes much more complicated if other tables are added to the model. The document available in the Microsoft PowerPivot for Excel 2010 Data Analysis Expressions Sample (which can be downloaded at http://tinyurl.com/DaxSample) shows a similar example in the Time Intelligence Functions section involving an Inventory Scenario with two tables other than the Dates table. Take a look at that document if you have a similar scenario.

Keep in mind that the Balances[Account] column used to make the relationship with the Accounts table is used in the LastBalanceTx formula and should not be selected in the PivotTable. Instead of that, you should use the Accounts[Account] column; otherwise, you could see wrong data in the PivotTable. The reason is similar to the case for which we suggest you use a Dates table instead of denormalizing all dates information in the same table that contains the measures. So a best practice is to hide in PivotTable all the columns that you use to relate Balances (the table containing measures) to other tables such as BalanceDate and Accounts (which are the tables containing attributes for browsing data).

Save to your account