DAX Scenarios in Power Pivot

Create custom calculations for a PivotTable

CALCULATE and CALCULATETABLE are powerful, flexible functions that useful for defining calculated fields. These functions let you change the context in which the calculation will be performed. You can also customize the type of aggregation or mathematical operation to perform. See the following topics for examples.

• CALCULATE

• CALCULATETABLE

Apply a filter to a formula

In most places where a DAX function takes a table as an argument, you can usually pass in a filtered table instead, either by using the FILTER function instead of the table name, or by specifying a filter expression as one of the function arguments. The following topics provide examples of how to create filters and how filters affect the results of formulas. For more information, see Filter Data in DAX Formulas.

The FILTER function lets you specify filter criteria by using an expression, while the other functions are designed specifically to filter out blank values.

• FILTER

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

Remove filters selectively to create a dynamic ratio

By creating dynamic filters in formulas, you can easily answer questions such as the following:

• What was the contribution of the current product's sales to total sales for the year?

• How much has this division contributed to total profits for all operating years, compared to other divisions?

Formulas that you use in a PivotTable can be affected by the PivotTable context, but you can selectively change the context by adding or removing filters. The example in the ALL topic shows you how to do this. To find the ratio of sales for a specific reseller over the sales for all resellers, you create a measure that calculates the value for the current context divided by the value for the ALL context.

The ALLEXCEPT topic provides an example of how to selectively clear filters on a formula. Both examples walk you through how the results change depending on the design of the PivotTable.

• ALL

• ALLEXCEPT

For other examples of how to calculate ratios and percentages, see the following topics:

• ISBLANK

• ISERROR

Using a value from an outer loop

In addition to using values from the current context in calculations, DAX can use a value from a previous loop in creating a set of related calculations. The following topic provides a walkthrough of how to build a formula that references a value from an outer loop. The EARLIER function supports up to two levels of nested loops.

• EARLIER Function

To learn more about row context and related tables, and how to use this concept in formulas, see Context in DAX Formulas.

Create a key column by concatenation

Power Pivot does not allow composite keys; therefore, if you have composite keys in your data source you may need to combine them into a single key column. The following topic provides one example of how to create a calculated column based on a composite key.

• CONCATENATE Function

Compose a date based on date parts extracted from a text date

Power Pivot uses a SQL Server date/time data type to work with dates; therefore, if your external data contains dates that are formatted differently -- for example, if your dates are written in a regional date format that is not recognized by the Power Pivot data engine, or if your data uses integer surrogate keys -- you may need to use a DAX formula to extract the date parts and then compose the parts into a valid date/time representation.

For example, if you have a column of dates that have been represented as an integer and then imported as a text string, you can convert the string to a date/time value by using the following formula:

=DATE(RIGHT([Value1],4),LEFT([Value1],2),MID([Value1],2))

The following topics provide more information about the functions used to extract and compose dates.

• DATEVALUE Function

• TIMEVALUE Function

• DATE Function

Define a custom date or number format

If your data contains dates or numbers that are not represented in one of the standard Windows text formats, you can define a custom format to ensure that the values are handled correctly. These formats are used when converting values to strings, or from strings. The following topics also provide a detailed list of the predefined formats that are available for working with dates and numbers.

• Pre-Defined Numeric Formats for the FORMAT Function

• Custom Numeric Formats for the FORMAT Function

• Pre-defined Date and Time formats for the FORMAT Function

• Custom Date and Time formats for the FORMAT Function

Change data types using a formula

In Power Pivot, the data type of the output is determined by the source columns, and you cannot explicitly specify the data type of the result, because the optimal data type is determined by Power Pivot. However, you can use the implicit data type conversions performed by Power Pivot to manipulate the output data type. 

• To convert a date or a number-string to a number, multiply by 1.0. For example, the following formula calculates the current date minus 3 days, and then outputs the corresponding integer value.

  =(TODAY()-3)*1.0

• To convert a date, number, or currency value to a string, concatenate the value with an empty string. For example, the following formula returns today's date as a string.

  =""& TODAY()

The following functions also can be used to ensure that a particular data type is returned:

Convert real numbers to integers

• ROUND Function

• CEILING Function

• FLOOR Function

  Convert real numbers, integers, or dates to strings

• FIXED Function

• FORMAT Function

  Convert strings to real numbers or dates

• VALUE Function

• DATEVALUE Function

• TIMEVALUE Function

Create a value based on a condition

You can use nested IF conditions to test values and generate new values conditionally. The following topics contain some simple examples of conditional processing and conditional values:

• IF

• OR Function

• CONCATENATE Function

Test for errors within a formula

Unlike Excel, you cannot have valid values in one row of a calculated column and invalid values in another row. That is, if there is an error in any part of a Power Pivot column, the entire column is flagged with an error, so that you must always correct formula errors that result in invalid values.

For example, if you create a formula that divides by zero, you might get the infinity result, or an error. Some formulas will also fail if the function encounters a blank value when it expects a numeric value. While you are developing your data model, it is best to allow the errors to appear so that you can click on the message and troubleshoot the problem. However, when you publish workbooks, you should incorporate error handling to prevent unexpected values from causing calculations to fail.

To avoid returning errors in a calculated column, you use a combination of logical and information functions to test for errors and always return valid values. The following topics provide some simple examples of how to do this in DAX:

• ISBLANK Function

• IFERROR Function

Calculate cumulative sales

The following topics contain examples of how to calculate closing and opening balances. The examples let you create running balances across different intervals such as days, months, quarters, or years.

• CLOSINGBALANCEMONTH Function, CLOSINGBALANCEQUARTER Function, CLOSINGBALANCEYEAR Function

• OPENINGBALANCEMONTH Function, OPENINGBALANCEQUARTER Function, OPENINGBALANCEYEAR Function

Compare values over time

The following topics contain examples of how to compare sums across different time periods. The default time periods supported by DAX are months, quarters, and years.

• PREVIOUSMONTH Function, PREVIOUSQUARTER, PREVIOUSYEAR Function

• TOTALMTD Function, TOTALQTD Function, TOTALYTD Function

• PARALLELPERIOD Function

Calculate a value over a custom date range

See the following topics for examples of how to retrieve custom date ranges, such as the first 15 days after the start of a sales promotion.

• DATESINPERIOD Function

• DATESBETWEEN Function

• DATEADD Function

• FIRSTDATE Function

• LASTDATE Function

If you use time intelligence functions to retrieve a custom set of dates, you can use that set of dates as input to a function that performs calculations, to create custom aggregates across time periods. See the following topic for an example of how to do this:

• PARALLELPERIOD Function

  Note: If you do not need to specify a custom date range, but are working with standard accounting units such as months, quarters or years, we recommend that you perform calculations by using the time intelligence functions designed for this purpose, such as TOTALQTD, TOTALMTD, TOTALQTD, etc.

Show only the top ten items in a PivotTable

Order items dynamically by using a formula

The following topic contains an example of how to use DAX to create a ranking that is stored in a calculated column. Because DAX formulas are calculated dynamically, you can always be sure that the ranking is correct even if the underlying data has changed. Also, because the formula is used in a calculated column, you can use the ranking in a Slicer and then select top 5, top 10, or even top 100 values.

• EARLIER Function.