Before advancing with this pattern, please make sure that you are already familiar with the following patterns:
Suppose that, in addition to analyzing the overall importance of a certain product in terms of ABC Classification, you also want to analyze the development of this product over time. In this case, you need a dynamic approach that does the segmentation separately for each time period. The general approach is the same as for the static ABC Classification, but now we calculate the segmentation on the fly in the context of all current filters. The final calculation is very flexible and works with any filter.
The data model shown in Figure 1 contains the following tables:
Note that the Classifications table does not have relationships with any other table. You will use a virtual relationship in the DAX calculation for the segmentation.
The Classifications table contains the values you can see in Figure 2.
The dynamic ABC Classification requires the definition of these four measures:
Sales Amount := SUM ( 'Sales'[SalesAmount] )
MinLowerBoundary := MIN ( 'Classifications'[LowerBoundary] )
MaxUpperBoundary := MAX ( 'Classifications'[UpperBoundary] )
Sales Amount ABC := CALCULATE ( [Sales Amount], VALUES ( 'Products'[ProductCode] ), FILTER ( CALCULATETABLE ( ADDCOLUMNS ( ADDCOLUMNS ( VALUES ( 'Products'[ProductCode] ), "OuterValue", [Sales Amount] ), "CumulatedSalesPercentage", DIVIDE ( SUMX ( FILTER ( ADDCOLUMNS ( VALUES ( 'Products'[ProductCode] ), "InnerValue", [Sales Amount] ), [InnerValue] >= [OuterValue] ), [InnerValue] ), CALCULATE ( [Sales Amount], VALUES ( 'Products'[ProductCode] ) ) ) ), ALL ( 'Products' ) ), [CumulatedSalesPercentage] > [MinLowerBoundary] && [CumulatedSalesPercentage] <= [MaxUpperBoundary] ) )
You can now use the Class column from the Classifications table together with the Sales Amount ABC measure to display the segments and their values. By further adding a slicer for the year, you can filter by Year(s) and recompute the classification based on the existing slicer selection. Thus, both the classification and the Sales Amount ABC measure are calculated with respect to the selected filter(s), as shown in Figure 3.
In Figure 4 you can see how a single product moves between segments over time.
The dynamic ABC Classification pattern is used to group items (such as Products or Customers) after they have been filtered by a given criteria. Usually, you use this classification in combination with time or region to find important items for a given selection.
In addition to all use cases that the static ABC Classification pattern covers (such as inventory management, customer segmentation, and marketing segmentation), the dynamic calculation further extends the pattern’s analytical capabilities by allowing slicing and dicing by all the other dimensions and hierarchies.
You can use the dynamic version of ABC Classification to track the performance of a product or set of products over time. This allows early identification of their declining or increasing importance for the business. This information can then be used to start a marketing campaign or to remove a product from sale.
You can identify regional differences between products and sets of products in terms of sales by using dynamic ABC Classification. This allows you to prioritize certain products and/or regions for sales and marketing, or simply helps you analyze data by region to understand your business better.
You can apply the dynamic ABC Classification pattern to any existing data model. It does not require any special arrangement of the data model; you will only need to add a table that defines the ABC segments and their boundaries.
First, you have to identify the measure to use for the segmentation. Such a measure must aggregate by using the SUM function in order for the dynamic ABC Classification pattern to work properly. A typical example would be the sum of sales.
<segmentation_measure> := SUM ( '<fact_table>'[<value_column>] )
The next step is to identify the table that contains the items to classify. This is the
You add a table (
You create the following measures to calculate the values of the boundaries for the currently selected segment:
[MinLowerBoundary] := MIN ( <lower_boundary> )
[MaxUpperBoundary] := MAX ( <upper_boundary> )
Once all these prerequisites are in place, you can create the final calculation:
<measure ABC> := CALCULATE ( <segmentation measure>, VALUES ( <business_key_column> ), FILTER ( CALCULATETABLE ( ADDCOLUMNS ( ADDCOLUMNS ( VALUES ( <business_key_column> ) "OuterValue", <segmentation_measure> ), "CumulatedValuePercentage", DIVIDE ( SUMX ( FILTER ( ADDCOLUMNS ( VALUES ( <business_key_column> ), "InnerValue", <segmentation_measure> ), [InnerValue] >= [OuterValue] ), [InnerValue] ), CALCULATE ( <segmentation_measure>, VALUES ( <business_key_column> ) ) ), ALL ( <item_table> ) ), [CumulatedValuePercentage] > [MinLowerBoundary] && [CumulatedValuePercentage] <= [MaxUpperBoundary] ) )
The final formula is rather complex. In order to make it easier to understand, let us break it down into separate logical steps.
Figure 5 illustrates the different steps of the evaluation process that performs the full operation of segmenting products and then calculating cumulated sales. The calculation order is from left to right.
You can change the ABC Classification pattern to fit specific needs. There are two common variations in a realworld scenario.
The basic ABC Classification (dynamic) pattern calculates the segments for each slice of the result set. For example, if you have years in columns, the segmentation is done for each year, as shown above in Figure 4. This way you can track performance of a single item over years. However, for some scenarios you might want to do the segmentation only in the context of the page filters, using the same segmentation for all the columns. For example, in Figure 6 you split sales of each segment over different regions.
As you can see, in North America, products in segment C are performing better than those in segment B. This is because the products are associated to the segments based on the value of the Grand Total column, where you can see the 70/20/10 split of ABC classification. A product with good total sales across all the continents (Grand Total) might have lower sales in a single region (e.g., North America). This type of segmentation can lead to valuable insights for marketing and sales departments.
In order to obtain this result, you add ALLSELECTED to the filter arguments of the CALCULATETABLE function:
Sales Amount ABC wPageFilter := CALCULATE ( <classification_measure>, VALUES ( <business_key_column> ), FILTER ( CALCULATETABLE ( ADDCOLUMNS ( ADDCOLUMNS ( VALUES ( <business_key_column> ) "OuterValue", <segmentation_measure> ), "CumulatedSalesPercentage", DIVIDE ( SUMX ( FILTER ( ADDCOLUMNS ( VALUES ( <business_key_column> ), "InnerValue", <segmentation_measure> ), [InnerValue] >= [OuterValue] ), [InnerValue] ), CALCULATE ( <segmentation_measure>, VALUES ( <business_key_column> ) ) ), ALL ( <item_table> ), ALLSELECTED ( ) ), [CumulatedSalesPercentage] > [MinLowerBoundary] && [CumulatedSalesPercentage] <= [MaxUpperBoundary] ) )
There might be scenarios where you first want to group your items and then do the segmentation within those groups. Suppose your products belong to different business areas, and you want to do a segmentation within those areas because the products themselves are independent of each other. For example, the pivot table in Figure 7 shows the segmentation within a particular group (the color Multi).
Across all groups, all the Multicolor items are in segment C (see values in the Sales Amount ABC column), because by default the segmentation applies to products of any color. However, you can do the segmentation within selected groups (e.g., products of Multi color) as shown in the Sales Amount ABC wGroup column. To do that, you change the calculation by using ALLEXCEPT instead of ALL. In this way, the segmentation happens within the currently selected group. In the following example,
Sales Amount ABC wGroup := CALCULATE ( <segmentation_measure>, VALUES ( <business_key_column> ), FILTER ( CALCULATETABLE ( ADDCOLUMNS ( ADDCOLUMNS ( VALUES ( <business_key_column> ) "OuterValue", <segmentation_measure> ), "CumulatedSalesPercentage", DIVIDE ( SUMX ( FILTER ( ADDCOLUMNS ( VALUES ( <business_key_column> ), "InnerValue", <segmentation_measure> ), [InnerValue] >= [OuterValue] ), [InnerValue] ), CALCULATE ( <segmentation_measure>, VALUES ( <business_key_column> ) ) ), ALLEXCEPT ( <item_table>, <group_column> ) ), [CumulatedSalesPercentage] > [MinLowerBoundary] && [CumulatedSalesPercentage] <= [MaxUpperBoundary] ) )
The list of variations is endless. You can use any filter by simply extending or changing the parameters of the CALCULATETABLE function. Hardcoded filters are also possible. For example, given the example in Figure 8, if you want to consider only products with a list price greater than 3,000, the
This is the corresponding pattern for this variation:
Sales Amount ABC wFixedFilter := CALCULATE ( <segmentation_measure>, VALUES ( <business_key_column> ), FILTER ( CALCULATETABLE ( ADDCOLUMNS ( ADDCOLUMNS ( VALUES ( <business_key_column> ) "OuterValue", <segmentation_measure> ), "CumulatedSalesPercentage", DIVIDE ( SUMX ( FILTER ( ADDCOLUMNS ( VALUES ( <business_key_column> ), "InnerValue", <segmentation_measure> ), [InnerValue] >= [OuterValue] ), [InnerValue] ), CALCULATE ( <segmentation_measure>, VALUES ( <business_key_column> ) ) ), ALL ( <item_table> ), <filter_column> >= <filter_value> ), [CumulatedSalesPercentage] > [MinLowerBoundary] && [CumulatedSalesPercentage] <= [MaxUpperBoundary] ) )]]>
Suppose you have an Agents table containing sales figures for each agent. The Parent column specifies the direct report of each agent, as you see in Figure 1.
Your goal is to create one calculated column for each level of the hierarchy. To create the right number of calculated columns, you must know in advance the maximum depth of the hierarchy. Otherwise, you have to estimate it, because this number cannot change dynamically. Power Pivot and Analysis Services hierarchies have an intrinsic limit of 64 levels.
Figure 3 shows the resulting table with the naturalized hierarchy.
You can create these columns in DAX by leveraging a hidden calculated column that provides a string with the complete path to reach the node in the current row of the table. The Path column in Figure 4 provides this content using the special PATH function.
[Path] = PATH ( Nodes[Name], Nodes[Parent] )
[Level1] = PATHITEM ( Nodes[Path], 1 ) [Level2] = PATHITEM ( Nodes[Path], 2 ) [Level3] = PATHITEM ( Nodes[Path], 3 )
You define the hierarchy in the diagram view of the data model shown in Figure 5.
You can navigate the resulting hierarchy in Excel as shown in Figure 6.
You can browse a pivot table and navigate this hierarchy down to the third level. If an intermediate node of the hierarchy has no children, you can still drilldown to an empty label, although this would result in a row of the pivot table with no description. You can avoid this behavior in Analysis Services Tabular by using the HideMemberIf property, and in Power Pivot by using the technique described in the complete pattern.
You can use the ParentChild Hierarchies pattern any time you have a corresponding structure in your source data. This is a list of common use cases.
Most profit and loss statements have a native parentchild hierarchy for representing the list of accounts. When this is not the native representation in the data source, a parentchild hierarchy can be useful to show an alternative custom grouping of original accounts, such as in balance sheet reclassification.
A list of components of a product is usually a native parentchild hierarchy, because each component has other subcomponents, with different levels of depth in different branches of the hierarchy. Calculations related to measures in a bill of materials are described in another dedicated pattern, Bills of Materials.
Company organizational structures are often represented as parentchild hierarchies. One of the limitations of parentchild hierarchies is that each node must have a single parent. Complex organizations that do not respect this constraint require more complex graphs, and mapping them to a parentchild hierarchy requires a normalization to a regular organization tree.
Suppose you have a Nodes table containing one row per node, with a ParentKey column that defines the parent of every node. The Transactions table has a manytoone relationship with the Nodes table. You can see the two tables in Figure 7.
You create a hidden HierarchyPath column containing the result of the PATH function, which provides a string with the complete path to reach the node in the current row of the table, as shown in Figure 8.
[HierarchyPath] = PATH ( Nodes[NodeKey], Nodes[ParentKey] )
You naturalize the hierarchy by creating a hidden column for each level of the hierarchy. You have to define the maximum depth of the hierarchy in advance, planning enough levels for future growth. For each level, you populate the column with the node name of the hierarchy path at that level. You need to duplicate the name of the leaf node if the level is higher than the number of levels in the hierarchy path, which you obtain in the HierarchyDepth column using the PATHLENGTH function.
In Figure 9 you can see the resulting column for the levels of the hierarchy, populated using the LOOKUPVALUE and PATHITEM functions. The hierarchy path is a string, but the LOOKUPVALUE has to match an integer column, so you need to cast the hierarchy path value to INT using the third argument of PATHITEM. The following formulas are used for the calculated columns in Figure 9.
[HierarchyDepth] = PATHLENGTH ( Nodes[HierarchyPath] ) [Level1] = LOOKUPVALUE ( Nodes[Name], Nodes[NodeKey], PATHITEM ( Nodes[HierarchyPath], 1, INTEGER ) ) [Level2] = IF ( Nodes[HierarchyDepth] >= 2, LOOKUPVALUE ( Nodes[Name], Nodes[NodeKey], PATHITEM ( Nodes[HierarchyPath], 2, INTEGER ) ), Nodes[Level1] ) [Level3] = IF ( Nodes[HierarchyDepth] >= 3, LOOKUPVALUE ( Nodes[Name], Nodes[NodeKey], PATHITEM ( Nodes[HierarchyPath], 3, INTEGER ) ), Nodes[Level2] )
In order to hide nodes duplicated at lower levels while browsing hierarchy in a pivot table, you create an IsLeaf calculated column, which contains a flag for nodes that have no children in the parentchild hierarchy, as you see in Figure 10.
[IsLeaf] = CALCULATE ( COUNTROWS ( Nodes ), ALL ( Nodes ), Nodes[ParentKey] = EARLIER ( Nodes[NodeKey] ) ) = 0
The naturalized hierarchy duplicates leaflevel nodes that you do not want to display in a pivot table. In an Analysis Services Tabular model, you can hide these nodes by setting the HideMemberIf property with BIDS Helper. In Power Pivot, you have to build a DAX measure that returns a blank value for a “duplicated” node. You create two hidden measures to support such a calculation: BrowseDepth calculates the level of the hierarchy displayed in a pivot table, and MaxNodeDepth returns the maximum depth of the original parentchild hierarchy starting at the node considered. When BrowseDepth is higher than MaxNodeDepth, the node value should be hidden in the pivot table. You can see in Figure 11 a comparison of the BrowseDepth and MaxNodeDepth values returned for each node of the naturalized hierarchy.
The Sales Amount Simple measure in Figure 11 displays blank when BrowseDepth value is higher than MaxNodeDepth.
[Sales Amount Simple] := IF ( [BrowseDepth] > [MaxNodeDepth], BLANK (), SUM ( Transactions[Amount] ) )
If you display such a measure in a pivot table with default settings (hiding empty rows), you will see a result like Figure 12.
The Sales Amount Simple measure does not display a separate value for intermediate nodes that have values associated both with children and with the node itself. For example, in Figure 12 you can see that the value related to Annabel is higher than the sum of her children, and the same happens for the value related to Brad. The reason is that both Annabel and Brad have directly related transactions. You can show the value for these nodes by implementing a more complex test, checking whether a leaf node has related transactions. The final Sales Amount measure considers all of these conditions, and its result is shown in Figure 13.
[Sales Amount] := IF ( [BrowseDepth] > [MaxNodeDepth] + 1, BLANK (), IF ( [BrowseDepth] = [MaxNodeDepth] + 1, IF ( AND ( VALUES ( Nodes[IsLeaf] ) = FALSE, SUM ( Transactions[Amount] ) <> 0 ), SUM ( Transactions[Amount] ), BLANK () ), SUM ( Transactions[Amount] ) ) )]]>
Suppose you have a Sales table containing one row for each row detail in an order, as shown in Figure 1.
You create the New Customers measure to count how many customers have never bought a product before, considering the past sales of only the current selection of products.
[New Customers] := COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( Sales[CustomerKey] ), "PreviousSales", CALCULATE ( COUNTROWS ( Sales ), FILTER ( ALL ( 'Date' ), 'Date'[FullDate] < MIN ( 'Date'[FullDate] ) ) ) ), [PreviousSales] = 0 ) )
In Figure 2 you can see the result of the New Customers measure: because the slicer is filtering only the Bikes category, the pivot table displays how many customers bought a bike for the first time in each month.
[Customers] := DISTINCTCOUNT ( Sales[CustomerKey] )
The Returning Customers measure counts how many customers made at least one purchase in the past and made another purchase in the current period.
[Returning Customers] := COUNTROWS ( CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), VALUES ( Sales[CustomerKey] ), FILTER ( ALL ( 'Date' ), 'Date'[FullDate] < MIN ( 'Date'[FullDate] ) ) ) )
You can also calculate the number of new customers by using the difference between the Customers and Returning Customers measures. This can be a good approach for simple formulas, but the technique used in this example is useful for implementing complex calculations, such as Lost Customers and Recovered Customers, as described in the complete pattern.
The New and Returning Customers pattern is useful in scenarios where you analyze customers’ behavior and loyalty based on recurring purchasing behaviors. The following is a list of some interesting use cases.
A company that uses customer retention strategies should analyze the results of its efforts by evaluating the behavior of its existing customer base. The New and Returning Customers pattern includes measures that enable such analysis, such as calculating the number of lost and returning customers. You can also analyze the results of actions targeted to lost customers by using the Recovered Customers measure.
If a company does not have a subscription model, identifying a lost customer requires the definition of a period of time within which the customer should make a purchase to be considered an active customer. Identifying lost customers is a key to analyzing customer attrition, customer churn, customer turnover, and customer defection.
To evaluate the churn rate of a customer base, you can use the New and Returning Customers pattern to obtain the number of new, lost, returning, and recovered customers.
You can analyze customer loyalty by using several measures of the New and Returning Customers pattern, such as Returning Customers and Lost Customers. Usually you evaluate these measures against the total number of customers, comparing the resulting ratio over time.
The complete pattern includes many different measures. Examples of implementation of all the measures are shown after the definitions and the data model below.
The measures used in the pattern are defined as follows:
For each measure, there is also an “absolute” version that ignores the current product selection for past purchases:
You need a data model similar to the one shown in Figure 3, with a Sales table that has a column containing a unique value for each customer (CustomerKey). If one customer has many keys (such as in a Slowly Changing Dimension Type 2, where every entity keeps the history of a changing attribute, storing one row for every version), you should denormalize the application key in the Sales table. The reason to do that is performance; you can avoid this denormalization by using a DAX calculation in the measure, but it would slow down query response time.
The pivot table in Figure 4 shows the relative measures of this pattern in the columns and the different periods in the rows.
The pivot table in Figure 5 shows the corresponding absolute measures of this pattern.
In the templates below, you will find the following markers:
There are two groups of templates:
Relative template measures define the state of a customer (new / lost / recovered / returning) considering only the products in the current selection and ignoring any transaction by the same customer with other products.
You can calculate the number of new customers as a difference between the customers in the current period and the returning customers.
[New Customers] := DISTINCTCOUNT ( <customer_key_column> )  [Returning Customers]
An alternative way to implement the same measure of new customers is by counting how many customers had no sales before the period selected.
[New Customers] := COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( <customer_key_column> ), "PreviousSales", CALCULATE ( COUNTROWS ( <fact_table> ), FILTER ( ALL ( <date_table> ), <date_column> < MIN ( <date_column> ) ) ) ), ISBLANK ( [PreviousSales] ) ) )
The former version of New Customers (subtraction between distinct count of customers and returning customers) is usually faster. However, it is a good idea to test the performance of both approaches depending on the distribution of data in the data model and the type of queries used, and then to choose the implementation that is faster on a casebycase basis.
You can evaluate the number of returning customers by using a technique that manipulates the filter context. The following measure combines two filters (the dates before the period selected, and the customers in the period selected) to count returning customers.
[Returning Customers] := COUNTROWS ( CALCULATETABLE ( VALUES ( <customer_key_column> ), VALUES ( <customer_key_column> ), FILTER ( ALL ( <date_table> ), <date_column> < MIN ( <date_column> ) ) ) )
Note Remember that the first argument of the CALCULATE or CALCULATETABLE function is the expression that will be evaluated in a filter context modified by the filter arguments. Passing VALUES (
) as a filter argument will keep the customers of the selected period in the filter context, once the filter over a different set of dates is applied. A possible bottleneck of the Returning Customers measure is that it applies a filter by date: if you are evaluating returning customers by month or year in a pivot table, the formula engine scans a materialized table including date and customer key. You might improve performance and reduce memory pressure by applying a filter over a month or year column instead of operating at the date level, possibly by denormalizing such a column in the fact table.
You can determine the number of lost customers by using the following calculation. The internal CustomerLostDate column sums the number of days since the last purchase of each customer, which defines when a customer is lost (the
[LostDaysLimit] := <days_lost_customer> + 1
[Lost Customers] := COUNTROWS ( FILTER ( ADDCOLUMNS ( FILTER ( CALCULATETABLE ( ADDCOLUMNS ( VALUES ( <customer_key_column> ), "CustomerLostDate", CALCULATE ( MAX ( <fact_date_column> ) ) + [LostDaysLimit] ), FILTER ( ALL ( <date_table> ), AND ( <date_column> < MIN ( <date_column> ), <date_column> >= MIN ( <date_column> )  [LostDaysLimit] ) ) ), AND ( AND ( [CustomerLostDate] >= MIN ( <date_column> ), [CustomerLostDate] <= MAX ( <date_column> ) ), [CustomerLostDate] <= CALCULATE ( MAX ( <fact_date_column> ), ALL ( <fact_table> ) ) ) ), "FirstBuyInPeriod", CALCULATE ( MIN ( <fact_date_column> ) ) ), OR ( ISBLANK ( [FirstBuyInPeriod] ), [FirstBuyInPeriod] > [CustomerLostDate] ) ) )
You can obtain the number of recovered customers with a calculation that compares, for each customer in the current selection, the date of the last purchase before the period selected against the first date of purchases in the current period.
Note The formula engine performs an important part of the calculation, so the DAX expression performs the most selective test in inner FILTER functions in order to minimize the occurrences of the least selective comparisons, improving performance when the number of recovered customers is high.
[Recovered Customers] := COUNTROWS ( FILTER ( ADDCOLUMNS ( FILTER ( FILTER ( ADDCOLUMNS ( VALUES ( <customer_key_column> ), "CustomerLostDate", CALCULATE ( MAX ( <fact_date_column> ), FILTER ( ALL ( <date_table> ), <date_column> < MIN ( <fact_date_column> ) ) ) ), NOT ( ISBLANK ( [CustomerLostDate] ) ) ), ( [CustomerLostDate] + [LostDaysLimit] ) < MAX ( <fact_date_column> ) ), "FirstBuyInPeriod", CALCULATE ( MIN ( <fact_date_column> ) ) ), [FirstBuyInPeriod] > ( [CustomerLostDate] + [LostDaysLimit] ) ) )
Absolute template measures define the state of a customer (new / lost / recovered / returning) considering all the purchases ever made by the same customer.
You can calculate the number of absolute new customers by counting how many customers had no transactions for any product before the period selected. In this case, you cannot use the difference between the customers in the current period and the returning customers.
[Absolute New Customers] := COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( <customer_key_column> ), "PreviousSales", CALCULATE ( COUNTROWS ( <fact_table> ), ALL ( <product_table> ), FILTER ( ALL ( <date_table> ), <date_column> < MIN ( <date_column> ) ) ) ), ISBLANK ( [PreviousSales] ) ) )
Important Note that the only difference between measures of new customers and absolute new customers is an additional filter argument, which is highlighted in the formula. If there are other attributes that you want to ignore for past transactions made by the same customer, you just include other ALL conditions on that table(s) in the filter arguments of the CALCULATE statement, removing filters on table/columns you want to ignore.
You can evaluate the number of absolute returning customers by adding ALL conditions in filter arguments of the Absolute Returning Customers measure, specifying the table/columns you do not want to filter in past transactions.
[Absolute Returning Customers] := COUNTROWS ( CALCULATETABLE ( VALUES ( <customer_key_column> ), VALUES ( <customer_key_column> ), ALL ( <product_table> ), FILTER ( ALL ( <date_table> ), <date_column> < MIN ( <date_column> ) ) ) )
To calculate the number of absolute lost customers, you add ALL conditions (for tables/attributes you want to ignore in past transactions) in two CALCULATETABLE and CALCULATE filter arguments of the original Lost Customers expression. Look at the explanation of the Lost Customers measure for more details about its behavior.
[LostDaysLimit] := <days_lost_customer> + 1
[Absolute Lost Customers] := COUNTROWS ( FILTER ( ADDCOLUMNS ( FILTER ( CALCULATETABLE ( ADDCOLUMNS ( VALUES ( <customer_key_column> ), "CustomerLostDate", CALCULATE ( MAX ( <fact_date_column> ) ) + [LostDaysLimit] ), ALL ( <product_table> ), FILTER ( ALL ( <date_table> ), AND ( <date_column> < MIN ( <date_column> ), <date_column> >= MIN ( <date_column> )  [LostDaysLimit] ) ) ), AND ( AND ( [CustomerLostDate] >= MIN ( <date_column> ), [CustomerLostDate] <= MAX ( <date_column> ) ), [CustomerLostDate] <= CALCULATE ( MAX ( <fact_date_column> ), ALL ( <fact_table> ) ) ) ), "FirstBuyInPeriod", CALCULATE ( MIN ( <fact_date_column> ), ALL ( <product_table> ) ) ), OR ( ISBLANK ( [FirstBuyInPeriod] ), [FirstBuyInPeriod] > [CustomerLostDate] ) ) )
Finally, you can count the number of absolute recovered customers (using the Absolute Recovered Customers measure) by adding ALL conditions (for tables/attributes to ignore in past transactions) in the filter argument of the only CALCULATE function included in the original Recovered Customers measure.
[Absolute Recovered Customers] := COUNTROWS ( FILTER ( ADDCOLUMNS ( FILTER ( FILTER ( ADDCOLUMNS ( VALUES ( <customer_key_column> ), "CustomerLostDate", CALCULATE ( MAX ( <fact_date_column> ), ALL ( <product_table> ), FILTER ( ALL ( <fact_date_column> ), <fact_date_column> < MIN ( <fact_date_column> ) ) ) ), NOT ( ISBLANK ( [CustomerLostDate] ) ) ), ( [CustomerLostDate] + [LostDaysLimit] ) < MAX ( <fact_date_column> ) ), "FirstBuyInPeriod", CALCULATE ( MIN ( <fact_date_column> ) ) ), [FirstBuyInPeriod] > ( [CustomerLostDate] + [LostDaysLimit] ) ) )
You can implement the measures of the New and Returning Customers pattern for the sample model based on Adventure Works after you create a column of Date data type (OrderDate) in the Sales table by using the following definition.
Sales[OrderDate] = RELATED ( 'Date'[FullDate] )
You can implement the measures of this pattern as follows.
[New Customers] := DISTINCTCOUNT ( Sales[CustomerKey] )  [Returning Customers]
[Returning Customers] := COUNTROWS ( CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), VALUES ( Sales[CustomerKey] ), FILTER ( ALL ( 'Date' ), 'Date'[FullDate] < MIN ( 'Date'[FullDate] ) ) ) )
[Lost Customers] := IF ( NOT ( MIN ( 'Date'[FullDate] ) > CALCULATE ( MAX ( Sales[OrderDate] ), ALL ( Sales ) ) ), COUNTROWS ( FILTER ( ADDCOLUMNS ( FILTER ( CALCULATETABLE ( ADDCOLUMNS ( VALUES ( Sales[CustomerKey] ), "CustomerLostDate", CALCULATE ( MAX ( Sales[OrderDate] ) ) + [LostDaysLimit] ), FILTER ( ALL ( 'Date' ), AND ( 'Date'[FullDate] < MIN ( 'Date'[FullDate] ), 'Date'[FullDate] >= MIN ( 'Date'[FullDate] )  [LostDaysLimit] ) ) ), AND ( AND ( [CustomerLostDate] >= MIN ( 'Date'[FullDate] ), [CustomerLostDate] <= MAX ( 'Date'[FullDate] ) ), [CustomerLostDate] <= CALCULATE ( MAX ( Sales[OrderDate] ), ALL ( Sales ) ) ) ), "FirstBuyInPeriod", CALCULATE ( MIN ( Sales[OrderDate] ) ) ), OR ( ISBLANK ( [FirstBuyInPeriod] ), [FirstBuyInPeriod] > [CustomerLostDate] ) ) ) )
[Recovered Customers] := COUNTROWS ( FILTER ( ADDCOLUMNS ( FILTER ( FILTER ( ADDCOLUMNS ( VALUES ( Sales[CustomerKey] ), "CustomerLostDate", CALCULATE ( MAX ( Sales[OrderDate] ), FILTER ( ALL ( 'Date' ), 'Date'[FullDate] < MIN ( Sales[OrderDate] ) ) ) ), NOT ( ISBLANK ( [CustomerLostDate] ) ) ), ( [CustomerLostDate] + [LostDaysLimit] ) < MAX ( Sales[OrderDate] ) ), "FirstBuyInPeriod", CALCULATE ( MIN ( Sales[OrderDate] ) ) ), [FirstBuyInPeriod] > ( [CustomerLostDate] + [LostDaysLimit] ) ) )
[Absolute New Customers] := COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( Sales[CustomerKey] ), "PreviousSales", CALCULATE ( COUNTROWS ( Sales ), ALL ( Product ), FILTER ( ALL ( 'Date' ), 'Date'[FullDate] < MIN ( 'Date'[FullDate] ) ) ) ), ISBLANK ( [PreviousSales] ) ) )
[Absolute Returning Customers] := COUNTROWS ( CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), VALUES ( Sales[CustomerKey] ), ALL ( Product ), FILTER ( ALL ( 'Date' ), 'Date'[FullDate] < MIN ( 'Date'[FullDate] ) ) ) )
[Absolute Lost Customers] := IF ( NOT ( MIN ( 'Date'[FullDate] ) > CALCULATE ( MAX ( Sales[OrderDate] ), ALL ( Sales ) ) ), COUNTROWS ( FILTER ( ADDCOLUMNS ( FILTER ( CALCULATETABLE ( ADDCOLUMNS ( VALUES ( Sales[CustomerKey] ), "CustomerLostDate", CALCULATE ( MAX ( Sales[OrderDate] ) ) + [LostDaysLimit] ), ALL ( Product ), FILTER ( ALL ( 'Date' ), AND ( 'Date'[FullDate] < MIN ( 'Date'[FullDate] ), 'Date'[FullDate] >= MIN ( 'Date'[FullDate] )  [LostDaysLimit] ) ) ), AND ( AND ( [CustomerLostDate] >= MIN ( 'Date'[FullDate] ), [CustomerLostDate] <= MAX ( 'Date'[FullDate] ) ), [CustomerLostDate] <= CALCULATE ( MAX ( Sales[OrderDate] ), ALL ( Sales ) ) ) ), "FirstBuyInPeriod", CALCULATE ( MIN ( Sales[OrderDate] ), ALL ( Product ) ) ), OR ( ISBLANK ( [FirstBuyInPeriod] ), [FirstBuyInPeriod] > [CustomerLostDate] ) ) ) )
[Absolute Recovered Customers]:= COUNTROWS ( FILTER ( ADDCOLUMNS ( FILTER ( FILTER ( ADDCOLUMNS ( VALUES ( Sales[CustomerKey] ), "CustomerLostDate", CALCULATE ( MAX ( Sales[OrderDate] ), ALL ( Product ), FILTER ( ALL ( 'Date' ), 'Date'[FullDate] < MIN ( Sales[OrderDate] ) ) ) ), NOT ( ISBLANK ( [CustomerLostDate] ) ) ), ( [CustomerLostDate] + [LostDaysLimit] ) < MAX ( Sales[OrderDate] ) ), "FirstBuyInPeriod", CALCULATE ( MIN ( Sales[OrderDate] ) ) ), [FirstBuyInPeriod] > ( [CustomerLostDate] + [LostDaysLimit] ) ) )
The only purpose of the initial IF statement in the measures for lost customers is to avoid evaluation for dates higher than the last date available in Sales, because the Date table contains more years than available data and a complete evaluation of lost customers for future dates would be useless and expensive.
In this section, you will see a few examples of the New and Returning Customers pattern.
Using a slicer, you can control the LostDaysLimit measure, which controls the evaluation of lost and recovered customers. This enables quick changes of the evaluation, trying different intervals between two sales to consider a customer as loyal. Figure 6 shows an example of the results obtained by defining a table (Days) containing a single column (Days Customer Lost) that is used to display the values in the slicer. The minimum value selected is the maximum distance between two sales before the customer is considered lost.
[DaysCustomerLost] := MIN ( Days[Days Customer Lost] )
[LostDaysLimit] := [DaysCustomerLost] + 1
You might want to filter a measure by considering only the new, returning, or recovered customers. For example, a pivot table could display the Sales Amount of different types of customers, as shown in Figure 7.
You define the measure using a slight variation of the original pattern. Instead of counting the rows returned by a table expression that filters the customers, you use the list of customers as a filter argument in a CALCULATE expression that evaluates the measure you want.
[Sales New Customers] := CALCULATE ( SUM ( Sales[SalesAmount] ), FILTER ( ADDCOLUMNS ( VALUES ( Sales[CustomerKey] ), "PreviousSales", CALCULATE ( COUNTROWS ( Sales ), FILTER ( ALL ( 'Date' ), 'Date'[FullDate] < MIN ( 'Date'[FullDate] ) ) ) ), ISBLANK ( [PreviousSales] ) ) )
[Sales Returning Customers] := CALCULATE ( SUM ( Sales[SalesAmount] ), CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), VALUES ( Sales[CustomerKey] ), FILTER ( ALL ( 'Date' ), 'Date'[FullDate] < MIN ( 'Date'[FullDate] ) ) ) )
[Sales Recovered Customers] := CALCULATE ( SUM ( Sales[SalesAmount] ), FILTER ( ADDCOLUMNS ( FILTER ( FILTER ( ADDCOLUMNS ( VALUES ( Sales[CustomerKey] ), "CustomerLostDate", CALCULATE ( MAX ( Sales[OrderDate] ), FILTER ( ALL ( 'Date' ), 'Date'[FullDate] < MIN ( Sales[OrderDate] ) ) ) ), NOT ( ISBLANK ( [CustomerLostDate] ) ) ), ( [CustomerLostDate] + [LostDaysLimit] ) < MAX ( Sales[OrderDate] ) ), "FirstBuyInPeriod", CALCULATE ( MIN ( Sales[OrderDate] ) ) ), [FirstBuyInPeriod] > ( [CustomerLostDate] + [LostDaysLimit] ) ) )
[Sales Loyal Customers] := CALCULATE ( SUM ( Sales[SalesAmount] ), FILTER ( ADDCOLUMNS ( FILTER ( FILTER ( ADDCOLUMNS ( VALUES ( Sales[CustomerKey] ), "CustomerLostDate", CALCULATE ( MAX ( Sales[OrderDate] ), FILTER ( ALL ( 'Date' ), 'Date'[FullDate] < MIN ( Sales[OrderDate] ) ) ) ), NOT ( ISBLANK ( [CustomerLostDate] ) ) ), ( [CustomerLostDate] + [LostDaysLimit] ) >= MIN ( Sales[OrderDate] ) ), "FirstBuyInPeriod", CALCULATE ( MIN ( Sales[OrderDate] ) ) ), [FirstBuyInPeriod] <= ( [CustomerLostDate] + [LostDaysLimit] ) ) )]]>
Suppose you have a Sales table containing one row for each row detail in an order. The SalesOrderNumber column identifies rows that are part of the same order, as you see in Figure 1.
[Orders] := DISTINCTCOUNT ( Sales[SalesOrderNumber] )
The Orders with Both Products measure implements a similar calculation, applying a filter that considers only the orders that also contain the product selected in the Filter Product table:
[Orders with Both Products] := CALCULATE ( DISTINCTCOUNT ( Sales[SalesOrderNumber] ), CALCULATETABLE ( SUMMARIZE ( Sales, Sales[SalesOrderNumber] ), ALL ( Product ), USERELATIONSHIP ( Sales[ProductCode], 'Filter Product'[Filter ProductCode] ) ) )
This is a generic approach to basket analysis, which is useful for many different calculations, as you will see in the complete pattern.
You can use the Basket Analysis pattern in scenarios when you want to analyze relationships between different rows in the same table that are connected by attributes located in the same table or in lookup tables. The following is a list of some interesting use cases.
With basket analysis, you can determine which products to offer to a customer, increasing revenues and improving your relationship with the customer. For example, you can analyze the products bought together by other customers, and then offer to your customer the products bought by others with a similar purchase history.
During the definition of an order, you can offer an upgrade, an addon, or a more expensive item. Basket analysis helps you to identify which product mix is more successful, based on order history.
You can use purchase history to define special promotions that combine or discount certain products. Basket analysis helps you to identify a successful product mix and to evaluate the success rate of a promotion encouraging customers to buy more products in the same order.
Create a data model like the one shown in Figure 4. The Filter Product table is a copy of the Product table and has the prefix “Filter” for each name (table, columns, hierarchies, and hierarchy levels). The relationship between the Sales and Filter Product tables is inactive. When doing basket analysis, users will use the Filter Product table to apply a second filter to product attributes.
The pivot table in Figure 5 shows the number of orders with different conditions depending on the selection of products made in rows and slicers.
The products hierarchy in the pivot table rows filters all the orders, and the Orders measure displays how many orders include at least one of the products selected (this is the number of orders that contain at least one of the products of the category/subcategory selected in the bold rows).
[Orders] := DISTINCTCOUNT ( Sales[SalesOrderNumber] )
The slicers around the pivot table show the selection of attributes from the Filter Product table (Filter Category, Filter Subcategory, and Filter Product). In this example, only one product is selected (Patch Kit/8 Patches). The Orders with Both Products measure shows how many orders contain at least one of the products selected in the pivot table rows and also the filtered product (Patch Kit/8 Patches) selected in the Filter Product slicer.
[Orders with Both Products] := CALCULATE ( DISTINCTCOUNT ( Sales[SalesOrderNumber] ), CALCULATETABLE ( SUMMARIZE ( Sales, Sales[SalesOrderNumber] ), ALL ( Product ), USERELATIONSHIP ( Sales[ProductCode], 'Filter Product'[Filter ProductCode] ) ) )
The Orders with Both Products % measure shows the percentage of orders having both products; it is obtained by comparing the Orders with Both Products measure with the Orders measure. However, if the product selected is the same in both the Product and Filter Product tables, you hide the result returning BLANK, because it is not useful to see 100% in these cases.
[SameProductSelection] := IF ( HASONEVALUE ( Product[ProductCode] ) && HASONEVALUE ( 'Filter Product'[Filter ProductCode] ), IF ( VALUES ( Product[ProductCode] ) = VALUES ( 'Filter Product'[Filter ProductCode] ), TRUE ) )
[Orders with Both Products %] := IF ( NOT ( [SameProductSelection] ), DIVIDE ( [Orders with Both Products], [Orders] ) )
The Orders without Both Products measure is simply the difference between Orders and Orders with Both Products measures. This number represents the number of orders containing the product selected in the pivot table, but not the product selected in the Filter Product slicer.
[Orders without Both Products] := [Orders]  [Orders with Both Products]
You can also create measures analyzing purchases made by each customer in different orders. The pivot table in Figure 6 shows the number of customers with different conditions depending on the selection of products in rows and slicers.
[Customers] := DISTINCTCOUNT ( Sales[CustomerKey] )
[Customers with Both Products] := CALCULATE ( DISTINCTCOUNT ( Sales[CustomerKey] ), CALCULATETABLE ( SUMMARIZE ( Sales, Sales[CustomerKey] ), ALL ( Product ), USERELATIONSHIP ( Sales[ProductCode], 'Filter Product'[Filter ProductCode] ) ) )
[Customers with Both Products %] := IF ( NOT ( [SameProductSelection] ), DIVIDE ( [Customers with Both Products], [Customers] ) )
To count or list the number of customers who never bought the product that you want to filter for, there is a more efficient technique. Use the Customers with No Filter Products measure, as defined below.
[Customers with No Filter Products] := COUNTROWS ( FILTER ( CALCULATETABLE ( Customer, Sales ), ISEMPTY ( CALCULATETABLE ( Sales, ALL ( Product ), USERELATIONSHIP ( Sales[ProductCode], 'Filter Product'[Filter ProductCode] ) ) ) ) )
You can use the result of the FILTER statement as a filter argument for a CALCULATE statement—for example, to evaluate the sales amount of the filtered selection of customers. If the ISEMPTY function is not available in your version of DAX, then use the following implementation:
[Customers with No Filter Products Classic] := COUNTROWS ( FILTER ( CALCULATETABLE ( Customer, Sales ), CALCULATE ( COUNTROWS ( Sales ), ALL ( Product ), USERELATIONSHIP ( Sales[ProductCode], 'Filter Product'[Filter ProductCode] ) ) = 0 ) )
ImportantThe ISEMPTY function is available only in Microsoft SQL Server 2012 Service Pack 1 Cumulative Update 4 or later versions. For this reason, ISEMPTY is available only in Power Pivot for Excel 2010 build 11.00.3368 (or later version) and SQL Server Analysis Services 2012 build 11.00.3368 (or later version). At the moment of writing, ISEMPTY is not available in any version of Excel 2013, updates of which depend on the Microsoft Office release cycle and not on SQL Server service packs.
In this section, you will see a few examples of the Basket Analysis pattern.
If you want to determine which products are most likely to be bought with another product, you can use a pivot table that sorts products by one of the percentage measures you have seen in the complete pattern. For example, you can use a slicer for Filter Product and place the product names in pivot table rows. You use either the Orders with Both Products % measure or the Customers with Both Products % measure to sort the products by rows, so that the first rows in the pivot table show the products that are most likely to be sold with the product selected in the slicer. The example shown in Figure 7 sorts products by the Customers with Both Products % measure in a descending way, so that you see which products are most commonly sold to customers who bought the product selected in the Filter Product slicer.
Once you know that certain products have a high chance to be bought together, you can obtain a list of customers who bought product A and never bought product B. You use two slicers, Product and Filter Product, selecting one or more products in each one. The pivot table has the customer names in rows. For customers who bought at least one product selected in the Product slicer but never bought any of the products selected in the Filter Product slicer, the Customers with No Filter Products measure should return 1.
The example shown in Figure 8 shows the list of customers who bought an HL Mountain Tire or an ML Mountain Tire, but never bought a Mountain Tire Tube. You may want to contact these customers and offer a Mountain Tire Tube.
]]>Suppose you have an Answers table containing the answers provided to a survey by customers defined in a Customers table. In the Answers table, every row contains an answer to a question. The first rows of the two tables are shown in Figure 1.
The Questions table in Figure 2 contains all the questions and possible answers, providing a unique key for each row. You can have questions with multiplechoice answers.
You import the Questions table twice, naming it Filter1 and Filter2. You rename the columns Question and Answer with a suffix identifying the filter they belong to. Every Filter table will become a possible slicer or filter in the pivot table used to query the survey data model. As you see in Figure 3, the relationships between the Answers table and Filter1 and Filter2 are inactive.
You need two filter tables to define a logical AND condition between two questions. For example, to count how many customers have a job as a teacher and play tennis, you need to apply a calculation such as the one described in the CustomersQ1andQ2 measure below.
[CustomersQ1andQ2] := CALCULATE ( COUNTROWS ( Customers ), CALCULATETABLE ( Answers, USERELATIONSHIP ( Answers[AnswerKey], Filter2[AnswerKey] ) ), CALCULATETABLE ( Answers, USERELATIONSHIP ( Answers[AnswerKey], Filter1[AnswerKey] ) ) )
Once you have this measure, you can use a pivot table to put answers from one question in rows, and put answers from another question in columns. The table in Figure 4 has sports in columns and jobs in rows, and you can see there are 16 customers who are tennisplaying teachers. The last column (Sport Practiced Total) shows how many customers practice at least one sport. For example, 33 teachers practice at least one sport.
If you want to compute the answers to just one question, you cannot use CustomersQ1andQ2, because it requires a selection from two different filters. Instead, use the CustomersQ1 measure, which computes the number of customers that answered the question selected in Filter1, regardless of what is selected in Filter2.
[CustomersQ1] := = CALCULATE ( COUNTROWS ( Customers ), CALCULATETABLE ( Answers, USERELATIONSHIP ( Answers[AnswerKey], Filter1[AnswerKey] ) ) )
In the DAX expression for CustomersQ1, you have to include the USERELATIONSHIP statement because the relationship with the Filter1 table in the data model is inactive, which is a required condition to perform the calculation defined in CustomersQ1andQ2. Figure 5 shows that there are 56 teachers, and you have seen in Figure 4 that only 33 of them practice at least one sport.
You can use the Survey pattern when you want to analyze correlations between events happening to the same entity. The following is a list of some interesting use cases.
A survey form usually has a set of questions with a predefined list of possible answers. You can have both singlechoice and multiplechoice questions. You want to analyze correlations between different questions in the same survey, using a single data model that does not change depending on the structure of the survey. The data in the tables define the survey structure so you do not need to create a different structure for every survey.
You can analyze the products bought together in the same transaction, although the Survey pattern can only identify existing relationships. A more specific Basket Analysis pattern is available to detect products that the same customer buys in different transactions.
You can structure many questions of an anamnesis questionnaire in a data model that corresponds to the Survey pattern. You can easily analyze the distribution of answers in a set of questionnaires by using a pivot table, with a data model that does not change when new questions are added to the questionnaire. The Survey pattern also handles multiplechoice answers without requiring a column for each answer (which is a common pattern used to adapt this type of data for analysis with Excel).
Create a data model like the one shown in Figure 183. You might replace the Customers table with one that represents an entity collecting answers (e.g., a Form table). It is important to use inactive relationships between the Answers and Filters tables.
You can calculate the answers to a single question, regardless of selections made on other filter tables, with the following measures:
CustomersQ1 := IF ( HASONEVALUE ( Filter1[Question 1] ), CALCULATE ( COUNTROWS ( Customers ), CALCULATETABLE ( Answers, USERELATIONSHIP ( Answers[AnswerKey], Filter1[AnswerKey] ) ) ) )
CustomersQ2 := IF ( HASONEVALUE ( Filter2[Question 2] ), CALCULATE ( COUNTROWS ( Customers ), CALCULATETABLE ( Answers, USERELATIONSHIP ( Answers[AnswerKey], Filter2[AnswerKey] ) ) ) )
The HASONEVALUE function checks whether the user selected only one question. If more than one question is selected in a filter table, the interpretation could be ambiguous: should you consider an AND or an OR condition between the two questions? The IF statement returns BLANK when multiple questions are selected within the same filter table.
Selecting multiple answers, however, is possible and it is always interpreted as an OR condition. For example, if the user selects both Baseball and Football answers for the Sport Practiced question, it means she wants to know how many customers practice baseball, or football, or both. This is the reason why the CALCULATE statement evaluates the number of rows in the Customers table, instead of counting the number of rows in the Answers table.
In case the user uses two filter tables, one question is possible for each filter. The answers to each question are considered in an OR condition, but the two questions are considered in an AND condition. For example, if the user selects Consultant and Teacher answers for the Job question in Filter1, and she selects Baseball and Football for the Sport Practiced question in Filter2, it means she wants to know how many customers who are consultants or teachers also practice baseball, or football, or both. You implement such a calculation with the following measure:
CustomersQ1andQ2 := SWITCH ( TRUE, NOT ( ISCROSSFILTERED ( Filter2[AnswerKey] ) ), [CustomersQ1], NOT ( ISCROSSFILTERED ( Filter1[AnswerKey] ) ), [CustomersQ2], IF ( HASONEVALUE ( Filter1[Question 1] ) && HASONEVALUE ( Filter2[Question 2] ), IF ( VALUES ( Filter2[Question 2] ) <> VALUES ( Filter1[Question 1] ), CALCULATE ( COUNTROWS ( Customers ), CALCULATETABLE ( Answers, USERELATIONSHIP ( Answers[AnswerKey], Filter2[AnswerKey] ) ), CALCULATETABLE ( Answers, USERELATIONSHIP ( Answers[AnswerKey], Filter1[AnswerKey] ) ) ) ) ) )
There are a few more checks in this formula in order to handle special conditions. If there are no filters active on the Filter2 table, then you can use the calculation for a single question, using the CustomersQ1 measure. In a similar way, if there are no filters active on the Filter1 table, you can use the CustomersQ2 measure. The ISCROSSFILTERED function just checks a column of the filter table to do that.
If a filter is active on both the Filter1 and Filter2 tables, then you want to calculate the number of customers satisfying the filters only if the user selected a single but different question in both Filter1 and Filter2; otherwise, you return a BLANK. For example, even if there are no filters on questions and answers in the pivot table rows in Figure 6, there are no duplicated rows with the answers to the Gender question, because we do not want to show an intersection between the same questions.
When you look at the result for a question without selecting an answer, the number you see is the number of unique customers who gave at least one answer to that question. However, it is important to consider that the data model always supports multiplechoice questions, even when the nature of the question is singlechoice. For example, the Gender question is a singlechoice one and the sum of Male and Female answers should correspond to the number of unique customers who answered the Gender question. However, you might have conflicting answers to the Gender question for the same customer. The data model does not provide any constraint that prevents such a conflict: you have to check data quality before importing data.
Important
Using a drillthrough action on measures used in the Survey pattern will produce unexpected results. The drillthrough only returns data filtered by active relationships in the data model, ignoring any further calculation or filter made through DAX expressions. If you want to obtain the list of customers that gave a particular combination of answers, you have to put the customer name in the pivot table rows and use slicers of pivot table filters to select the desired combination of questions and answers.
When you use slicers to display a selection of questions and answers, remember that there is slightly different behavior between Excel 2010 and Excel 2013. If you have a slicer with questions and another with answers for the same filter, you would like the slicer for the answers to display only the possible choices for the selected question. In Excel 2010, you can only change the position of the answers, so that possible choices for the selected question are displayed first in the slicer: to do that, set the Show Items With No Data Last checkbox (in the Slicer Settings dialog box shown in Figure 7).
Using this setting, the Female and Male answers for the selected Gender question are displayed first in the Answer1 slicer, as you see in Figure 8.
With Excel 2013, you can hide the answers belonging to questions that are not selected, by setting the Hide Items With No Data checkbox shown in Figure 9.
In this way, the Answer1 slicer does not display answers unrelated to the selection made in the Question1 slicer, as you see in Figure 10.
]]>Suppose you have sales data at the day and product level, and the budget for each product at the year level, as shown in Figure 1.
[Total Sales] := SUM ( Sales[SalesAmount] )
You define the measures related to the budget in the Sales table, hiding the Budget table from the client tools. In this way, you will not have a warning about a missing relationship in the data model while browsing data.
Using the DAX function ISFILTERED, you check whether the current filter on Date has the same year granularity of the budget or if it has a higher granularity. When the granularity is different, the measure IsBudgetValid returns FALSE, and you need to allocate the budget on months or days.
[IsBudgetValid] := NOT ( ISFILTERED ( 'Date'[Date] )  ISFILTERED ( 'Date'[Month] )  ISFILTERED ( 'Date'[MonthNumber] ) )
The Budget measure returns the sum of BudgetAmount if the budget granularity is compatible with the budget definition. If the granularity is different, then you must compute the budget using an allocation algorithm, which depends on business requirements. In this example, you allocate the same budget for each working day in the year. For each year, you calculate the ratio between the working days in the current filter and the total working days in the year. The SUMX function iterates over the years selected, and for each year retrieves the corresponding budget using a FILTER that simulates the relationship at the year granularity.
[Budget] := IF ( [IsBudgetValid], SUM ( Budget[BudgetAmount] ), SUMX ( VALUES ( 'Date'[Year] ), CALCULATE ( COUNTROWS ( 'Date' ), 'Date'[WorkingDay] = TRUE ) / CALCULATE ( COUNTROWS ( 'Date' ), ALLEXCEPT ( 'Date', 'Date'[Year] ), 'Date'[WorkingDay] = TRUE ) * CALCULATE ( SUM ( Budget[BudgetAmount] ), FILTER ( ALL ( Budget[Year] ), Budget[Year] = 'Date'[Year] ) ) ) )
The variance between Total Sales and Budget is a trivial measure. Using DIVIDE, you avoid the division by zero error in case the budget is not available.
[Var%] := DIVIDE ( [Total Sales]  [Budget], [Budget] )
You can see the results of the Total Sales, Budget, and Var% measures in Figure 3. You split the budget by month according to the number of working days in each month.
You can use the budget patterns whenever you have a table with data of one granularity and you want to allocate numbers to a different granularity, based on an allocation algorithm that meets your business requirements.
If you want to evenly split a yearly budget into a monthly budget, you use a fixed allocation. For example, you divide the year value by 12 in order to obtain the monthly value, or you divide the year value by 365 (or 366 for leap years) and then multiply the result by the number of days in each month. In either case, you have an allocation that is deterministic and depends only on the calendar.
You might want to use historical data to allocate the budget over attributes that are not available as levels of the budget. For example, if you have a budget defined by product category, you can allocate the budget by product according to the ratio of sales between each product and the corresponding product category for the previous year. You can allocate the budget for multiple attributes at the same time—for example, also for the date, to obtain a seasonal allocation based on previous sales.
The Budget table and the measure it is being compared to may define data at different granularity levels. For example, the budget shown in Figure 4 is defined by product category and month/year, whereas the sales quantity is available by day, product, and territory. The budget does not include the territory at all.
'Date'[YearMonth] = 'Date'[Year] * 100 + 'Date'[MonthNumber]
Budget[YearMonth] = Budget[Year] * 100 + Budget[Month]
[Quantity] := SUM ( Sales[OrderQuantity] )
[Budget] := IF ( [IsBudgetValid], [BudgetCalc], [AllocatedBudget] )
[Var%] := DIVIDE ( [Quantity]  [Budget], [Budget] )
You can check whether the current filter has a granularity corresponding to the one available in the Budget table by counting the number of rows in the Sales table in two conditions: with the current filter context and by removing the filters on granularities that are not available in the budget itself. If the two numbers are different, than the budget is not valid at the granularity of the current filter context. You implement the IsBudgetValid measure with the following template, using these markers:
[IsBudgetValid] := ( COUNTROWS ( <fact_table> ) = CALCULATE ( COUNTROWS ( <fact_table> ), ALL ( <fact_table> ), VALUES ( <lookup_granularity_column_1> ), ... VALUES ( <lookup_granularity_column_N> ) ) )
You have to include a filter argument in the CALCULATE function for each logical relationship you have between Budget and other tables. You implement the IsBudgetValid measure in the example by using YearMonth and Category columns, without specifying any filter argument for the columns in the Territory table, since it is not included in the budget definition.
[IsBudgetValid] := ( COUNTROWS ( Sales ) = CALCULATE ( COUNTROWS ( Sales ), ALL ( Sales ), VALUES ( 'Date'[YearMonth] ), VALUES ( Product[Category] ) ) )
If the filter context is compatible with the budget granularity, you calculate the budget by applying the filters defined by the existing logical relationships. This is the pattern described in the Simulate a Relationship at Different Granularities section of the Handling Different Granularities pattern. You define the BudgetCalc measure as follows:
[BudgetCalc] := CALCULATE ( SUM ( Budget[Budget] ), FILTER ( ALL ( Budget[YearMonth] ), CONTAINS ( VALUES ( 'Date'[YearMonth] ), 'Date'[YearMonth], Budget[YearMonth] ) ), FILTER ( ALL ( Budget[Category] ), CONTAINS ( VALUES ( Product[category] ), Product[Category], Budget[Category] ) ) )
When you need to allocate the budget to different granularities, you iterate over the granularity of the budget, applying an allocation factor to each value of the budget. You perform the iteration over the result of a CROSSJOIN of the logical relationships available in the Budget table. You can apply the following template, using this marker:
[AllocatedBudget] := SUMX ( CROSSJOIN ( VALUES ( <lookup_granularity_column_1> ), ... VALUES ( <lookup_granularity_column_N> ), ), [AllocationFactor] * [BudgetCalc] )
For example, you implement the AllocatedBudget measure in this scenario using the following definition:
[AllocatedBudget] := SUMX ( CROSSJOIN ( VALUES ( 'Date'[YearMonth] ), VALUES ( Product[Category] ) ), [AllocationFactor] * [BudgetCalc] )
The calculation of the allocation factor is the core of the allocation algorithm. You evaluate the ratio of a reference measure between its value calculated in the current filter context and its value calculated in the available granularity of the budget. The denominator of the ratio uses a CALCULATE function that applies a filter argument for each table that has a direct relationship with the data that are used to calculate the reference measure. You can apply the following template, using these markers:
[AllocationFactor] := <reference_measure> / CALCULATE ( <reference_measure>, <filter_budget_granularity_1>, ... <filter_budget_granularity_N> )
The reference measure might correspond to the actual value that the budget is compared with, but calculated in a different period (e.g., budget sales allocated according to the sales of the previous year), or it could be another measure calculated in the same period (e.g., budget costs allocated according to the revenues of the same period).
Each filter argument you pass to the CALCULATE statement in the denominator of the AllocationFactor measure has to remove the parts of the filter context that are not available in the granularity of the budget. For example, if you have the budget by month and product category, you have to remove the filters for day, product name, and product subcategory, so that the remaining filters match the budget granularity. You apply a different technique for each table that has a direct relationship with the table containing data used by the measure. In the data model of this example, the Quantity measure sums the QuantitySold column from the Sales table, so you consider only the tables having a relationship with Sales: Date, Product, and Territory.
Three different filterremoval templates are available, depending on the logical relationship of each table with the budget:
ALLEXCEPT ( <table>, <lookup_granularity_column> )
ALL ( <table> ), VALUES ( <lookup_granularity_column> ) )
ALL ( <table> )
The markers used in the previous templates are the following:
You apply an ALLEXCEPT or ALL / VALUES template for each lookup granularity column you have in the CROSSJOIN arguments of the AllocatedBudget measure, and you apply the ALL template to all the other tables involved.
In this example, you allocate the budget based on the quantity sold in the previous year, which is the QuantityPY reference measure. You remove the filter from all the Product columns except for Category and ID_Category (these columns have the same granularity). You also remove the filters from all the Date columns except for YearMonth, using the ALL / VALUES template instead of ALLEXCEPT because the table is marked as Date table. The special behavior of DAX for Date tables requires a different approach than ALLEXCEPT, which would not produce correct results in this particular case. Finally, you apply the ALL template to the Territory table, which has a relationship with Sales, but is not part of the budget granularity.
You implement the AllocationFactor and QuantityPY measures using the following definitions:
[AllocationFactor] := [QuantityPY] / CALCULATE ( [QuantityPY], ALLEXCEPT ( Product, Product[Category], Product[ID_Category] ), ALL ( 'Date' ), VALUES ( 'Date'[YearMonth] ), ALL ( Territory ) )
[QuantityPY] := CALCULATE ( [Quantity], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
You test the budget calculation for all the columns involved in allocation. The budget defined by year and month aggregates value at the year and quarter level, whereas it allocates the month value at the day level. For example, the budget in March 2014 allocates by day using the quantity sold in March 2013, as you see in Figure 8.
Suppose you have sales data at the day level and advertising expenses at the month level. The data source assigns the entire month’s advertising cost to the first day of each month, as shown in Figure 1.
You have a common referenced Date table, based on the Date column. However, you do not want to display the sum of AdvertisingAmount at the day level, because this would produce the result in Figure 2. Such a display would suggest that the advertising happens only the first day of each month. You want to see the value of advertisements only at the month level, not at the day level.
You can create a measure that hides the value when there is a filter or selection at the day granularity level.
[Total Advertising] := IF ( NOT ( ISFILTERED ( 'Date'[Date] ) ), SUM ( Advertising[AdvertisingAmount] ) )
The ISFILTERED function returns true if there is a filter active on the column specified in the argument. The Date table used in this example has only Year, Month, and Date columns. If you have other day columns, such as Day Number or Week Day Name, you should check whether a filter is active on these columns, too.
By using the Total Advertising measure, you do not show the value of advertising at the day level, as shown in Figure 3.
You use this technique whenever you want to show a calculation only at a valid granularity level.
You can use the Handling Different Granularities pattern whenever you want to hide a measure or the result of an expression, based on the current selection. For example, if you defined the budget at the month level, and you do not have an algorithm that allocates the monthly budget at the day level, you can hide the difference between sales and budget at the day level.
When you have a budget to compare with data, and the budget table does not have the same granularity as the other measures, you must accommodate the different granularities. This is common for time periods: the sales budget may be defined at the month or quarter level, whereas revenues are available at the day level. It happens also for other attributes, such as the product: the budget may be defined at the product category level, but not at the unit (SKU) level. Moreover, you probably have other attributes, such as the customer or the payment type, for which you do not have a budget definition at all.
You should hide measures that compare budget and revenues if the granularity is not available for both measures. For example, you should not display a key performance indicator (KPI) that shows the revenues vs. the planned goal if the user browsing data selects a single product (or day) but the budget is available only at the product category (or month) level. The Handling Different Granularities pattern helps you to implement this control. If you want to allocate the budget for higher granularities, you should consider the Budget pattern.
When you compare sales and purchases in the same data model, you probably have a different set of attributes for these measures. When certain attributes are selected, you cannot calculate a measure that evaluates the differences between cost and revenues. For example, you probably know the customer for the sales and the vendor for the purchases. If a report contains a selection of one or more customers, you might not be able to evaluate how much of the purchases are related to those sales. Thus, you hide the measure that compares sales and purchases if a selection is active on a customer or on a vendor.
You have to handle data at different granularities when at least two tables contain different levels of stored information. For example, Figure 4 shows one table with SalesAmount recorded at the day level, whereas AdvertisingAmount is in another table with one value for each month.
If you import these tables in a data model and try to link them to a common Date table, it will not work. In fact, as you see in Figure 5, the Advertising table does not have a Date column available to create the relationship with the Date table.
You can solve the problem by creating a fictitious date that allows you to define the missing relationship. Such a date could be the first day of the corresponding month. You define the calculated column Date in the Advertising table with the following formula. You can see the result in Figure 6.
[Date] = DATE ( Advertising[Year], Advertising[Month], 1 )
Once you have the Date column, you can create the relationship between Advertising and Date, as you see in Figure 7.
Since the Date you created is not a real one, when browsing data you should hide any measure derived from the Advertising table if the evaluation happens at the day level. The only valid levels are Month and Year. Thus, you have to define the Total Advertising measure by checking whether the Date column is filtered in the Date table or not:
[Total Advertising] := IF ( NOT ( ISFILTERED ( 'Date'[Date] ) ), SUM ( Advertising[AdvertisingAmount] ) )
In case there was a WeekDay column or a MonthDayNumber in the Date table, you should include these columns in the test, as in the following example:
[Total Advertising] := IF ( NOT ( ISFILTERED ( 'Date'[Date] )  ISFILTERED ( 'Date'[WeekDay] )  ISFILTERED ( 'Date'[MonthDayNumber] ) ), SUM ( Advertising[AdvertisingAmount] ) )
As a general pattern, you apply the following template to return a blank value when the current selection is not valid for the granularity of a measure, using these markers:
[Checked Measure] := IF ( NOT ( ISFILTERED ( <invalid_granularity_column_1> )  ISFILTERED ( <invalid_granularity_column_2> ) ...  ISFILTERED ( <invalid_granularity_column_N> ) ), <unchecked measure> )
Once you have created a checked measure, other calculations based on that measure might avoid duplicating the test on granularity, as soon as the blank propagates into the result in the DAX expression used. For example, the Advertising% measure can be defined in this way, and returns blank when the Total Advertising is blank, as you see in Figure 8.
Advertising % := DIVIDE ( [Total Advertising], [Total Sales] )
This section demonstrates how to handle different granularities without a relationship between tables.
You can use DAX to filter data as if a relationship exists between two tables, even if there is no relationship between them in the data model. This is useful whenever a logical relationship does not correspond to a physical one.
The data model you use in DAX allows you to create relationships using only one column, which needs to be the key of the lookup table. When you have data at different granularities, you also have a logical relationship that would involve a hierarchical level not corresponding to the key of the lookup table. For instance, in the previous example of the pattern, you assign a specific date for a measure that has a month granularity (e.g., Advertising), and in order to display data in the correct way, you hide the measure when the user browses data at a day level instead of a month level. An alternative approach is to write a DAX measure that “simulates” a relationship between Advertising and Date tables at the month level.
The scenario is the same as was in Figure 5: the Advertising table cannot have a relationship with the Date table. This time, you do not create a fictitious Date column to create the relationship. Instead, you create a YearMonth column in both Date and Advertising table, so that you have a column representing the granularity of the logical relationship between the tables. You use a single column to simplify the required DAX code and improve performance.
You define the YearMonth calculated column in the Advertising and Date tables as follows. The resulting Advertising table is shown in Figure 9.
Advertising[YearMonth] = Advertising[Year] * 100 + Advertising[Month] 'Date'[YearMonth] = 'Date'[Year] * 100 + 'Date'[MonthNumber]
The YearMonth column exists in the Date and Advertising tables, but a physical relationship is not possible because you might have many rows with the same value in both tables. You might create a third table having YearMonth granularity, but this could be confusing for the user and would not help the calculation much. The resulting data model is the one shown in Figure 10.
The Total Advertising measure has to filter the sum of AdvertisingAmount using a CALCULATE function. You pass a filter argument with the list of YearMonth values that have to be included in the calculation, and you implement this using a FILTER that iterates all the YearMonth values in the Advertising table, keeping only those with at least a corresponding value in Date[YearMonth] that is active in the current filter context.
[Total Advertising] := IF ( NOT ( ISFILTERED ( 'Date'[Date] ) ), CALCULATE ( SUM ( Advertising[AdvertisingAmount] ), FILTER ( ALL ( Advertising[YearMonth] ), CONTAINS ( VALUES ( 'Date'[YearMonth] ), 'Date'[YearMonth], Advertising[YearMonth] ) ) ) )
When you browse the data, the result is the same as shown in Figure 8.
In general, you apply the following template to return a measure filtered by applying a logical relationship through columns that are not keys in any of the tables involved, using these markers:
[Filtered Measure] := CALCULATE ( <target_measure>, FILTER ( ALL ( <target_granularity_column> ), CONTAINS ( VALUES ( <lookup_granularity_column> ), <lookup_granularity_column>, <target_granularity_column> ) ) )
If you have many logical relationships, you pass one FILTER argument to CALCULATE for each relationship.
[Filtered Measure] := CALCULATE ( <target_measure>, FILTER ( ALL ( <target_granularity_column_1> ), CONTAINS ( VALUES ( <lookup_granularity_column_1> ), <lookup_granularity_column_1>, <target_granularity_column_1> ) ), ... FILTER ( ALL ( <target_granularity_column_N> ), CONTAINS ( VALUES ( <lookup_granularity_column_N > ), <lookup_granularity_column_N >, <target_granularity_column_N > ) ) )
In the Total Advertising measure, you keep the initial IF statement that checks whether to display the data at the current selected granularity or not. If you omit this control, the value would be propagated to other granularities (e.g., you would see the monthly Advertising value repeated for all the days in the month).
]]>Suppose you want to analyze the importance of products for the revenues of your company using ABC analysis. You have to assign each product to a class (A, B, or C) for which the following is true:
In the Products table, you create a calculated column that contains the ABC class to use as a grouping attribute in reports. The Products table has a relationship with the Sales table.
To implement ABC classification, you then create a few more calculated columns in the Products table. All of these columns except ABC Class will be hidden from the client tools:
You define the calculated columns using the following DAX formulas:
[ProductSales] = CALCULATE ( SUM ( Sales[SalesAmount] ) ) [CumulatedSales] = CALCULATE ( SUM ( Products[ProductSales] ), ALL ( Products ), Products[ProductSales] >= EARLIER ( Products[ProductSales] ) ) [CumulatedPercentage] = Products[CumulatedSales] / SUM ( Products[ProductSales] ) [ABC Class] = SWITCH ( TRUE (), Products[CumulatedPercentage] <= 0.7, "A", Products[CumulatedPercentage] <= 0.9, "B", "C" )
You can use the new ABC Class column as a filter in a pivot tables, as shown in Figure 103 and Figure 104.
You use the ABC classification to create a static segmentation of entities in a data model. If the entity you want to classify does not have the granularity of a table, you have to use slightly different formulas, as described in the Complete Pattern section.
You can use the ABC Classification pattern whenever you want to focus attention on a smaller number of elements in a set—for example, when you have to allocate limited resources in a more efficient way. The following is a small list of common use cases, but real world applications are countless.
You can use ABC classification as an inventory categorization technique to help manage stock and reduce overall inventory cost. Items in class A are the most important for the business and you should analyze their value more often, whereas items in class C are less important and items in class B are in an intermediate state. For example, you might increase the stock availability and negotiate for better prices for products in class A, reducing time and resources for items in classes B and C.
The measure used as a target for ABC classification in inventory management might include multiple criteria that consider volume (sales amount), profitability (contribution margin on inventory investment), and velocity (number of times an item is ordered).
You can use ABC classification of customers to calibrate resources allocated for sales and marketing, such as investment on customer retention policies, prioritization of technical support calls, assignment of dedicated account managers, and so on. The measures used as a target for classification are usually revenue and margin.
You might use ABC classification to segment products for allocating marketing budget used to promote and push product sales. The measures used as a target for classification are usually revenue and margin, whereas the item considered can be the SKU of the product or a group of features (e.g., category, model, color, and so on).
You calculate the ABC classification for an entity with the following template, using these markers:
[EntityMeasure] = CALCULATE ( <measure> ) [CumulatedPercentage] = CALCULATE ( <measure>, ALL ( <granularity_table> ), <granularity_table>[EntityMeasure] >= EARLIER ( <granularity_table>[EntityMeasure] ) ) / CALCULATE ( <measure>, ALL ( <granularity_table> ) ) [ABC Class] = SWITCH ( TRUE (), <granularity_table>[CumulatedPercentage] <= 0.7, "A", <granularity_table>[CumulatedPercentage] <= 0.9, "B", "C" )
For example, you would implement the ABC Product calculated column in a model with Products and Sales tables as follows:
[ProductSales] = CALCULATE ( [Sales Amount] ) [ProductPercentage] = CALCULATE ( [Sales Amount], ALL ( Products ), Products[ProductSales] >= EARLIER ( Products[ProductSales] ) ) / CALCULATE ( [Sales Amount], ALL ( Products ) ) [ABC Product] = SWITCH ( TRUE (), Products[ProductPercentage] <= 0.7, "A", Products[ProductPercentage] <= 0.9, "B", "C" )
If you want to calculate the ABC classification for an attribute of the entity, you use a slightly different template only for the EntityMeasure calculated column:
[EntityMeasure] = CALCULATE ( <measure>, ALL ( <granularity_table> ), <granularity_table>[<granularity_attribute>] = EARLIER( <granularity_table>[<granularity_attribute>] ) )
For example, you implement the ABC Model calculated column in the same model with Products and Sales tables as follows:
[ModelSales] = CALCULATE ( [Sales Amount], ALL ( Products ), Products[ProductModel] = EARLIER ( Products[ProductModel] ) ) [ModelPercentage] = CALCULATE ( [Sales Amount], ALL ( Products ), Products[ModelSales] >= EARLIER ( Products[ModelSales] ) ) / CALCULATE ( [Sales Amount], ALL ( Products ) ) [ABC Model] = SWITCH ( TRUE (), Products[ModelPercentage] <= 0.7, "A", Products[ModelPercentage] <= 0.9, "B", "C" )
All the products belonging to the same model share the same ABC Model classification.
To use ABC classification on a single denormalized table, you must slightly change the EntityMeasure definition as follows:
[EntityMeasure] = CALCULATE ( <measure>, ALLEXCEPT ( <granularity_table>, <granularity_table>[<granularity_attribute>] ) )
For example, you would implement ABC Product and ABC Model calculated columns in a model with a single denormalized Sales table as follows:
[ProductSales] = CALCULATE ( [Sales Amount], ALLEXCEPT ( Sales, Sales[Product] ) ) [ProductPercentage] = CALCULATE ( [Sales Amount], ALL ( Sales ), Sales[ProductSales] >= EARLIER ( Sales[ProductSales] ) ) / CALCULATE ( [Sales Amount], ALL ( Sales ) ) [ABC Product] = SWITCH ( TRUE, Sales[ProductPercentage] <= 0.7, "A", Sales[ProductPercentage] <= 0.9, "B", "C" ) [ModelSales] = CALCULATE ( [Sales Amount], ALLEXCEPT ( Sales, Sales[Model] ) ) [ModelPercentage] = CALCULATE ( [Sales Amount], ALL ( Sales ), Sales[ModelSales] >= EARLIER ( Sales[ModelSales] ) ) / CALCULATE ( [Sales Amount], ALL ( Sales ) ) [ABC Model] = SWITCH ( TRUE (), Products[ModelPercentage] <= 0.7, "A", Products[ModelPercentage] <= 0.9, "B", "C" )]]>
Suppose you want to provide the user with a calculation of yeartodate sales without relying on DAX time intelligence functions. You need a relationship between the Date and Sales tables, as shown in Figure 1.
Sales := SUM ( Sales[SalesAmount] )
The yeartodate calculation must replace the filter over the Date table, using a filter argument in a CALCULATE function. You can use a FILTER that iterates all the rows in the Date table, applying a logical condition that returns only the days that are less than or equal to the maximum date present in the current filter context, and that belong to the last selected year.
You define the SalesYTD measure as follows:
[SalesYTD] := CALCULATE ( [Sales], FILTER ( ALL ( 'Date' ), 'Date'[Year] = MAX ( 'Date'[Year] ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
You can see the result of Sales and SalesYTD in Figure 2.
Important
If you are not using time intelligence functions, the presence of ALL ( ‘Date’ ) in the calculate filter automatically produces the same effect as a table marked as a date table in the data model. In fact, filtering in a CALCULATE function the date column used to mark a date table would implicitly add the same ALL ( ‘Date’ ) that you see explicitly defined in this pattern. However, when you implement custom timerelated calculations, it is always a good practice to mark a table as a date table, even if you do not use the DAX time intelligence functions.
You can use the Time Intelligence pattern whenever using the standard DAX time intelligence functions is not an option (for example, if you need a custom calendar). The pattern is very flexible and moves the business logic of the timerelated calculations from the DAX predefined functions to the content of the Date table. The following is a list of some interesting use cases.
The time intelligence functions in DAX (such as TOTALYTD, SAMEPERIODLASTYEAR, and many others) assume that every day in a month belongs to the same quarter regardless of the year. This assumption is not valid for a weekbased calendar, in which each quarter and each year might contain days that are not “naturally” related. For example, in an ISO 8601 calendar, January 1 and January 2 of 2011 belong to week 52 of year 2010, and the first week of 2011 starts on January 3. This approach is common in the retail and manufacturing industries, where the 445 calendar, 544 calendar, and 454 calendar are used. By using 445 weeks in a quarter, you can easily compare uniform numbers between quarters, mainly because you have the same number of working days and weekends in each quarter. You can find further information about these calendars on Wikipedia (see the 445 calendar and ISO week date pages). The Time Intelligence pattern can handle any type of custom calendar. You can find also a specific implementation of weekbased calendar pattern in the WeekBased Time Intelligence in DAX article published on SQLBI.
When you use the DirectQuery feature in an Analysis Services Tabular model, DAX queries are converted into SQL code sent to the underlying SQL Server data source, but the DAX time intelligence functions are not available. You can use the Time Intelligence pattern to implement timerelated calculations using DirectQuery.
The Date table must contain all the attributes used in the calculation, in a numeric format. For example, the fiscal calendar shown in Figure 3 has strings for visible columns (Year, Month, Quarter, and Week Day), along with corresponding numeric values in other columns (YearNumber, MonthNumber, QuarterNumber, and WeekDayNumber). You will hide the numeric values from client tools in the data model but use them to implement time intelligence calculations in DAX and to sort the string columns.
These additional columns are visible in Figure 4.
Any aggregation over time filters the Date table to include all the dates in the period considered. The only difference in each formula is the condition that checks whether the date belongs to the considered aggregation or not.
The general formula will be:
[AggregationOverTime] := CALCULATE ( [OriginalMeasure], FILTER ( ALL ( 'Date' ), <check whether the date belongs to the aggregation> ) )
When you define an aggregation, usually you extend the period considered to include all the days elapsed since a particular day in the past. However, it is best to not make any assumption about the calendar structure, instead writing a condition that entirely depends on the data in the table. For example, you can write the yeartodate in this way:
[YTD] := CALCULATE ( [OriginalMeasure], FILTER ( ALL ( 'Date' ), 'Date'[Year] = MAX ( 'Date'[Year] ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
However, a calculation of the last 12 months would be more complicated, because there could be leap years (with February having 29 days instead of 28) and the year might not start on January 1. A calculated column can have information that simplifies the condition. For example, the SequentialDayNumber column contains the running total of days in the Date table, excluding February 29. This is the formula used to define such a calculated column:
= COUNTROWS ( FILTER ( ALL ( Date ), 'Date'[Date] <= EARLIER ( 'Date'[Date] ) && NOT ( MONTH ( 'Date'[Date] ) = 2 && DAY ( 'Date'[Date] ) = 29 ) ) )
When the formula is written in this way, February 29 will have always the same SequentialDayNumber as February 28. You can write the moving annual total (the total of the last 12 months) as the total of the last 365 days. Since the test is based on SequentialDayNumber, February 29 will be automatically included in the range, which will consider 366 days instead of 365.
[MAT Sales] := CALCULATE ( [Sales], FILTER ( ALL ( 'Date' ), 'Date'[SequentialDayNumber] > MAX ( 'Date'[SequentialDayNumber] )  365 && 'Date'[SequentialDayNumber] <= MAX ( 'Date'[SequentialDayNumber] ) ) )
A complete list of calculations is included in the More Patterns section.
You can write the calculation for an aggregation by simply using the date, even if the SequentialDayNumber column is required to handle leap years. The period comparison can be more complex, because it requires detection of the current selection in order to apply the correct filter on dates to get a parallel period. For example, to calculate the yearoveryear difference, you need the value of the same selection in the previous year. This analysis complicates the DAX formula required, but it is necessary if you want a behavior similar to the DAX time intelligence functions for your custom calendar.
The following implementation assumes that the calendar month drives the logic to select a corresponding comparison period. If the user selects all the days in a month, that entire month will be selected in a related period (a month, quarter, or year back in time) for the comparison. If instead she selects only a few days in a month, then only the corresponding days in the same month will be selected in the related period. You can implement a different logic (based on weeks, for example) by changing the filter expression that selects the days to compare with.
For example, the monthovermonth calculation (MOM) compares the current selection with the same selection one month before.
[MOM Sales] := [Sales] – [PM Sales] [MOM% Sales] := DIVIDE ( [MOM Sales], [PM Sales] )
The complex part is the calculation of the corresponding selection for the previous month (PM Sales). The formula iterates the YearMonthNumber column, which contains a unique value for each month and year.
SUMX ( VALUES ( 'Date'[YearMonthNumber] ), <calculation for the month> )
The calculation is different depending on whether all the days of the month are included in the selection or not. So the first part of the calculation performs this check.
IF ( CALCULATE ( COUNTROWS ( VALUES ( 'Date'[Date] ) ) ) = CALCULATE ( VALUES ( 'Date'[MonthDays] ) ), <calculation for all days selected in the month>, <calculation for partial selection of the days in the month> )
If the number of days selected is equal to the number of days in the month (stored in the MonthDays column), then the filter selects all the days in the previous month (by subtracting one from the YearMonthNumber column).
CALCULATE ( [Sales], ALL ( 'Date' ), FILTER ( ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] )  1 ) )
Otherwise, the filter also includes the days selected in the month iterated (MonthDayNumber column); such a filter is highlighted in the following formula.
CALCULATE ( [Sales], ALL ( 'Date' ), CALCULATETABLE ( VALUES ( 'Date'[MonthDayNumber] ) ), FILTER ( ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] )  1 ) ) )
The complete formula for the sales in the previous month is as follows.
[PM Sales] := SUMX ( VALUES ( 'Date'[YearMonthNumber] ), IF ( CALCULATE ( COUNTROWS ( VALUES ( 'Date'[Date] ) ) ) = CALCULATE ( VALUES ( 'Date'[MonthDays] ) ), CALCULATE ( [Sales], ALL ( 'Date' ), FILTER ( ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] )  1 ) ), CALCULATE ( [Sales], ALL ( 'Date' ), CALCULATETABLE ( VALUES ( 'Date'[MonthDayNumber] ) ), FILTER ( ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] )  1 ) ) ) )
The other calculations for previous quarter and previous year simply change the number of months subtracted in the filter on YearMonthNumber column. The complete formulas are included in the More Pattern Examples section.
Semiadditive measures require a particular calculation when you compare data over different periods. The simple calculation requires the LASTDATE function, which you can use also with custom calendars:
[Balance] := CALCULATE ( [Inventory Value], LASTDATE ( 'Date'[Date] ) )
However, if you want to avoid any time intelligence calculation due to incompatibility with DirectQuery mode, you can use the following syntax:
[Balance DirectQuery] := CALCULATE ( [Inventory Value], FILTER ( 'Date'[Date], 'Date'[Date] = MAX ( 'Date'[Date] ) ) )
You do not need to compute aggregations over time for semiadditive measures because of their nature: you need only the last day of the period and you can ignore values in other days. However, a different calculation is required if you want to compare a semiadditive measure over different periods. For example, if you want to compare the last day in two different monthbased periods, you need a more complex logic to identify the last day because the months may have different lengths. A simple solution is to create a calculated column for each offset you want to handle, directly storing the corresponding date in the previous month, quarter, or year. For example, you can obtain the corresponding “last date” in the previous month with this calculated column:
'Date'[PM Date] = CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date' ), FILTER ( ALL ( 'Date'[MonthDayNumber] ), 'Date'[MonthDayNumber] <= EARLIER ( 'Date'[MonthDayNumber] )  EARLIER ( 'Date'[MonthDayNumber] ) = EARLIER ( 'Date'[MonthDays] ) ), FILTER ( ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) – 1 ) )
The logic behind the formula is that you consider the last available date in the previous month, so that if it has fewer days than the current month, you get the last available one. For example, for March 30, you will get February 28 or 29. However, if the previous month has more days than the current month, you still get the last day available, thanks to the condition that does not filter any MonthDayNumber if it is equal to MonthDays, which is the number of days in the current month. For example, for September 30, you will obtain August 31 as a result. You will just change the comparison to YearMonthNumber if you want to get the previous quarter or year, using 3 or 12, respectively, instead of 1 in this filter of [PQ Date] and [PY Date] calculated columns:
'Date'[PQ Date] = ... 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) – 3 ... 'Date'[PY Date] = ... 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) – 12 ...
Having an easy way to get the corresponding last date of the previous month, you can now write a short definition of the previous month Balance measure, by just using MAX ( Date[PM Date] ) to filter the date:
[PM Balance] := CALCULATE ( Inventory[Inventory Value], FILTER ( ALL ( 'Date' ), 'Date'[Date] = MAX ( 'Date'[PM Date] ) ) )
You can define the measures for the previous quarter and previous year just by changing the measure used in the MAX function, using [PQ Date] and [PY Date], respectively. These columns are useful also for implementing the comparison of aggregations over periods, such as Month Over Month To Date, as shown in the More Pattern Examples section.
This section shows the time patterns for different types of calculations that you can apply to a custom monthlybased calendar without relying on DAX time intelligence functions. The measures defined will use the following naming convention:
Acronym  Description  Shift Period  Aggregation  Comparison 

YTD  Year To Date 
X 

QTD  Quarter To Date 
X 

MTD  Month To Date 
X 

MAT  Moving Annual Total 
X 

PY  Previous Year 
X 

PQ  Previous Quarter 
X 

PM  Previous Month 
X 

PP  Previous Period (automatically selects year, quarter, or month) 
X 

PMAT  Previous Year Moving Annual Total 
X 
X 

YOY  Year Over Year 
X 

QOQ  Quarter Over Quarter 
X 

MOM  Month Over Month 
X 

POP  Period Over Period (automatically selects year, quarter, or month) 
X 

AOA  Moving Annual Total Over Moving Annual Total 
X 
X 

PYTD  Previous Year To Date 
X 
X 

PQTD  Previous Quarter To Date 
X 
X 

PMTD  Previous Month To Date 
X 
X 

YOYTD  Year Over Year To Date 
X 
X 
X 
QOQTD  Quarter Over Quarter To Date 
X 
X 
X 
MOMTD  Month Over Month To Date 
X 
X 
X 
The formulas in this section define the different aggregations over time.
[PY Sales] := SUMX ( VALUES ( 'Date'[YearMonthNumber] ), IF ( CALCULATE ( COUNTROWS ( VALUES ( 'Date'[Date] ) ) ) = CALCULATE ( VALUES ( 'Date'[MonthDays] ) ), CALCULATE ( [Sales], ALL ( 'Date' ), FILTER ( ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) – 12 ) ), CALCULATE ( [Sales], ALL ( 'Date' ), CALCULATETABLE ( VALUES ( 'Date'[MonthDayNumber] ) ), FILTER ( ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) – 12 ) ) ) )
[PQ Sales] := SUMX ( VALUES ( 'Date'[YearMonthNumber] ), IF ( CALCULATE ( COUNTROWS ( VALUES ( 'Date'[Date] ) ) ) = CALCULATE ( VALUES ( 'Date'[MonthDays] ) ), CALCULATE ( [Sales], ALL ( 'Date' ), FILTER ( ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) – 3 ) ), CALCULATE ( [Sales], ALL ( 'Date' ), CALCULATETABLE ( VALUES ( 'Date'[MonthDayNumber] ) ), FILTER ( ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) – 3 ) ) ) )
[PM Sales] := SUMX ( VALUES ( 'Date'[YearMonthNumber] ), IF ( CALCULATE ( COUNTROWS ( VALUES ( 'Date'[Date] ) ) ) = CALCULATE ( VALUES ( 'Date'[MonthDays] ) ), CALCULATE ( [Sales], ALL ( 'Date' ), FILTER ( ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) – 1 ) ), CALCULATE ( [Sales], ALL ( 'Date' ), CALCULATETABLE ( VALUES ( 'Date'[MonthDayNumber] ) ), FILTER ( ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) – 1 ) ) ) )
[PP Sales] := SWITCH ( TRUE, ISFILTERED ( 'Date'[Month] ), [PM Sales], ISFILTERED ( 'Date'[Quarter] ), [PQ Sales], ISFILTERED ( 'Date'[Year] ), [PY Sales], BLANK () )
[YOY Sales] := [Sales]  [PY Sales]
[QOQ Sales] := [Sales]  [PQ Sales]
[MOM Sales] := [Sales]  [PM Sales]
[POP Sales] := [Sales]  [PP Sales]
[YOY% Sales] := DIVIDE ( [YOY Sales], [PY Sales] )
[QOQ% Sales] := DIVIDE ( [QOQ Sales], [PQ Sales] )
[MOM% Sales] := DIVIDE ( [MOM Sales], [PM Sales] )
[POP% Sales] := DIVIDE ( [POP Sales], [PP Sales] )
[Balance] := CALCULATE ( Inventory[Inventory Value], FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] = MAX ( 'Date'[Date] ) ) )
[PY Balance] := CALCULATE ( Inventory[Inventory Value], FILTER ( ALL ( 'Date' ), 'Date'[Date] = MAX ( 'Date'[PY Date] ) ) )
[PQ Balance] := CALCULATE ( Inventory[Inventory Value], FILTER ( ALL ( 'Date' ), 'Date'[Date] = MAX ( 'Date'[PQ Date] ) ) )
[PM Balance] := CALCULATE ( Inventory[Inventory Value], FILTER ( ALL ( 'Date' ), 'Date'[Date] = MAX ( 'Date'[PM Date] ) ) )
[PP Balance] := CALCULATE ( Inventory[Inventory Value], FILTER ( ALL ( 'Date' ), 'Date'[Date] = MAX ( 'Date'[PP Date] ) ) )
[YOY Balance] := [Balance]  [PY Balance]
[QOQ Balance] := [Balance]  [PQ Balance]
[MOM Balance] := [Balance]  [PM Balance]
[POP Balance] := [Balance]  [PP Balance]
[YOY% Balance] := DIVIDE ( [YOY Balance], [PY Balance] )
[QOQ% Balance] := DIVIDE ( [QOQ Balance], [PQ Balance] )
[MOM% Balance] := DIVIDE ( [MOM Balance], [PM Balance] )
[POP% Balance] := DIVIDE ( [POP Balance], [PP Balance] )
The formulas in this section define the different aggregations over time.
[Sales] := SUM ( Sales[SalesAmount] )
[YTD Sales] := CALCULATE ( [Sales], FILTER ( ALL ( DATE ), 'Date'[YearNumber] = MAX ( 'Date'[YearNumber] ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
[QTD Sales] := CALCULATE ( [Sales], FILTER ( ALL ( DATE ), 'Date'[YearQuarterNumber] = MAX ( 'Date'[YearQuarterNumber] ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
[MTD Sales] := CALCULATE ( [Sales], FILTER ( ALL ( DATE ), 'Date'[YearMonthNumber] = MAX ( 'Date'[YearMonthNumber] ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
[MAT Sales] := CALCULATE ( [Sales], FILTER ( ALL ( 'Date' ), 'Date'[SequentialDayNumber] > MAX ( 'Date'[SequentialDayNumber] )  365 && 'Date'[SequentialDayNumber] <= MAX ( 'Date'[SequentialDayNumber] ) ) )
The measures that combine aggregation and period comparison are implemented using the calculated columns (in the Date table) that return the corresponding date in a previous period (year, quarter, and month).
[PYTD Sales] := CALCULATE ( [Sales], FILTER ( ALL ( DATE ), 'Date'[YearNumber] = MAX ( 'Date'[YearNumber] ) – 1 && 'Date'[Date] <= MAX ( 'Date'[PY Date] ) ) )
[PQTD Sales] := CALCULATE ( [Sales], FILTER ( ALL ( DATE ), 'Date'[YearQuarterNumber] = MAX ( 'Date'[YearQuarterNumber] ) – 1 && 'Date'[Date] <= MAX ( 'Date'[PQ Date] ) ) )
[PMTD Sales] := CALCULATE ( [Sales], FILTER ( ALL ( DATE ), 'Date'[YearMonthNumber] = MAX ( 'Date'[YearMonthNumber] ) – 1 && 'Date'[Date] <= MAX ( 'Date'[PM Date] ) ) )
[PMAT Sales] := CALCULATE ( [Sales], FILTER ( ALL ( 'Date' ), 'Date'[SequentialDayNumber] > MAX ( 'Date'[SequentialDayNumber] ) – 730 && 'Date'[SequentialDayNumber] <= MAX ( 'Date'[SequentialDayNumber] ) – 365 ) )
[YOYTD Sales] := [YTD Sales]  [PYTD Sales]
[QOQTD Sales] := [QTD Sales]  [PQTD Sales]
[MOMTD Sales] := [MTD Sales]  [PMTD Sales]
[AOA Sales] := [MAT Sales]  [PMAT Sales]
[YOYTD% Sales] := DIVIDE ( [YOYTD Sales], [PYTD Sales] )
[QOQTD% Sales] := DIVIDE ( [QOQTD Sales], [PQTD Sales] )
[MOMTD% Sales] := DIVIDE ( [MOMTD Sales], [PMTD Sales] )
[AOA% Sales] := DIVIDE ( [AOA Sales], [PMAT Sales] )]]>
The Statistical Patterns are a collection of common statistical calculations: median, mode, moving average, percentile, and quartile. We would like to thank Colin Banfield, Gerard Brueckl, and Javier Guillén, whose blogs inspired some of the following patterns.
The formulas in this pattern are the solutions to specific statistical calculations.
You can use standard DAX functions to calculate the mean (arithmetic average) of a set of values.
The moving average is a calculation to analyze data points by creating a series of averages of different subsets of the full data set. You can use many DAX techniques to implement this calculation. The simplest technique is using AVERAGEX, iterating a table of the desired granularity and calculating for each iteration the expression that generates the single data point to use in the average. For example, the following formula calculates the moving average of the last 7 days, assuming that you are using a Date table in your data model.
Moving AverageX 7 Days := AVERAGEX ( DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), 7, DAY ), [Total Amount] )
Using AVERAGEX, you automatically calculate the measure at each granularity level. When using a measure that can be aggregated (such as SUM), then another approach—based on CALCULATE—may be faster. You can find this alternative approach in the complete pattern of Moving Average.
You can use standard DAX functions to calculate the variance of a set of values.
You can use standard DAX functions to calculate the standard deviation of a set of values.
The median is the numerical value separating the higher half of a population from the lower half. If there is an odd number of rows, the median is the middle value (sorting the rows from the lowest value to the highest value). If there is an even number of rows, it is the average of the two middle values. The formula ignores blank values, which are not considered part of the population. The result is identical to the MEDIAN function in Excel.
Median := ( MINX ( FILTER ( VALUES ( Data[Value] ), CALCULATE ( COUNT ( Data[Value] ), Data[Value] <= EARLIER ( Data[Value] ) ) > COUNT ( Data[Value] ) / 2 ), Data[Value] ) + MINX ( FILTER ( VALUES ( Data[Value] ), CALCULATE ( COUNT ( Data[Value] ), Data[Value] <= EARLIER ( Data[Value] ) ) > ( COUNT ( Data[Value] )  1 ) / 2 ), Data[Value] ) ) / 2
Figure 1 shows a comparison between the result returned by Excel and the corresponding DAX formula for the median calculation.
The mode is the value that appears most often in a set of data. The formula ignores blank values, which are not considered part of the population. The result is identical to the MODE and MODE.SNGL functions in Excel, which return only the minimum value when there are multiple modes in the set of values considered. The Excel function MODE.MULT would return all of the modes, but you cannot implement it as a measure in DAX.
Mode := MINX ( TOPN ( 1, ADDCOLUMNS ( VALUES ( Data[Value] ), "Frequency", CALCULATE ( COUNT ( Data[Value] ) ) ), [Frequency], 0 ), Data[Value] )
Figure 2 compares the result returned by Excel with the corresponding DAX formula for the mode calculation.
The percentile is the value below which a given percentage of values in a group falls. The formula ignores blank values, which are not considered part of the population. The calculation in DAX requires several steps, described in the Complete Pattern section, which shows how to obtain the same results of the Excel functions PERCENTILE, PERCENTILE.INC, and PERCENTILE.EXC.
The quartiles are three points that divide a set of values into four equal groups, each group comprising a quarter of the data. You can calculate the quartiles using the Percentile pattern, following these correspondences:
A few statistical calculations have a longer description of the complete pattern, because you might have different implementations depending on data models and other requirements.
Usually you evaluate the moving average by referencing the day granularity level. The general template of the following formula has these markers:
The simplest pattern uses the AVERAGEX function in DAX, which automatically considers only the days for which there is a value.
Moving AverageX <number_of_days> Days:= AVERAGEX ( FILTER ( ALL ( <date_column> ), <date_column> > ( MAX ( <date_column> )  <number_of_days> ) && <date_column> <= MAX ( <date_column> ) ), <measure> )
As an alternative, you can use the following template in data models without a date table and with a measure that can be aggregated (such as SUM) over the entire period considered.
Moving Average <number_of_days> Days:= CALCULATE ( IF ( COUNT ( <date_column> ) >= <number_of_days>, SUM ( Sales[Amount] ) / COUNT ( <date_column> ) ), FILTER ( ALL ( <date_column> ), <date_column> > ( MAX ( <date_column> )  <number_of_days> ) && <date_column> <= MAX ( <date_column> ) ) )
The previous formula considers a day with no corresponding data as a measure that has 0 value. This can happen only when you have a separate date table, which might contain days for which there are no corresponding transactions. You can fix the denominator for the average using only the number of days for which there are transactions using the following pattern, where:
Moving Average <number_of_days> Days No Zero:= CALCULATE ( IF ( COUNT ( <date_column> ) >= <number_of_days>, SUM ( Sales[Amount] ) / CALCULATE ( COUNT ( <date_column> ), <fact_table> ) ), FILTER ( ALL ( <date_column> ), <date_column> > ( MAX ( <date_column> )  <number_of_days> ) && <date_column> <= MAX ( <date_column> ) ) )
You might use the DATESBETWEEN or DATESINPERIOD functions instead of FILTER, but these work only in a regular date table, whereas you can apply the pattern described above also to nonregular date tables and to models that do not have a date table.
For example, consider the different results produced by the following two measures.
Moving Average 7 Days := CALCULATE ( IF ( COUNT ( 'Date'[Date] ) >= 7, SUM ( Sales[Amount] ) / COUNT ( 'Date'[Date] ) ), FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] > ( MAX ( 'Date'[Date] )  7 ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
Moving Average 7 Days No Zero := CALCULATE ( IF ( COUNT ( 'Date'[Date] ) >= 7, SUM ( Sales[Amount] ) / CALCULATE ( COUNT ( 'Date'[Date] ), Sales ) ), FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] > ( MAX ( 'Date'[Date] )  7 ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
In Figure 3, you can see that there are no sales on September 11, 2005. However, this date is included in the Date table; thus, there are 7 days (from September 11 to September 17) that have only 6 days with data.
The measure Moving Average 7 Days has a lower number between September 11 and September 17, because it considers September 11 as a day with 0 sales. If you want to ignore days with no sales, then use the measure Moving Average 7 Days No Zero. This could be the right approach when you have a complete date table but you want to ignore days with no transactions. Using the Moving Average 7 Days formula, the result is correct because AVERAGEX automatically considers only nonblank values.
Keep in mind that you might improve the performance of a moving average by persisting the value in a calculated column of a table with the desired granularity, such as date, or date and product. However, the dynamic calculation approach with a measure offers the ability to use a parameter for the number of days of the moving average (e.g., replace <number_of_days> with a measure implementing the Parameters Table pattern).
The median corresponds to the 50^{th} percentile, which you can calculate using the Percentile pattern. However, the Median pattern allows you to optimize and simplify the median calculation using a single measure, instead of the several measures required by the Percentile pattern. You can use this approach when you calculate the median for values included in <value_column>, as shown below:
Median := ( MINX ( FILTER ( VALUES ( <value_column> ), CALCULATE ( COUNT ( <value_column> ), <value_column> <= EARLIER ( <value_column> ) ) > COUNT ( <value_column> ) / 2 ), <value_column> ) + MINX ( FILTER ( VALUES ( <value_column> ), CALCULATE ( COUNT ( <value_column> ), <value_column> <= EARLIER ( <value_column> ) ) > ( COUNT ( <value_column> )  1 ) / 2 ), <value_column> ) ) / 2
To improve performance, you might want to persist the value of a measure in a calculated column, if you want to obtain the median for the results of a measure in the data model. However, before doing this optimization, you should implement the MedianX calculation based on the following template, using these markers:
MedianX := ( MINX ( TOPN ( COUNTROWS ( CALCULATETABLE ( <granularity_table>, <measure_table> ) ) / 2, CALCULATETABLE ( <granularity_table>, <measure_table> ), <measure> 0 ), <measure> ) + MINX ( TOPN ( ( COUNTROWS ( CALCULATETABLE ( <granularity_table>, <measure_table> ) ) + 1 ) / 2, CALCULATETABLE ( <granularity_table>, <measure_table> ), <measure>, 0 ), <measure> ) ) / 2
For example, you can write the median of Internet Total Sales for all the Customers in Adventure Works as follows:
MedianX := ( MINX ( TOPN ( COUNTROWS ( CALCULATETABLE ( Customer, 'Internet Sales' ) ) / 2, CALCULATETABLE ( Customer, 'Internet Sales' ), [Internet Total Sales], 0 ), [Internet Total Sales] ) + MINX ( TOPN ( ( COUNTROWS ( CALCULATETABLE ( Customer, 'Internet Sales' ) ) + 1 ) / 2, CALCULATETABLE ( Customer, 'Internet Sales' ), [Internet Total Sales], 0 ), [Internet Total Sales] ) ) / 2
Tip The following pattern:
CALCULATETABLE ( <granularity_table>, <measure_table> )is used to remove rows from <granularity_table> that have no corresponding data in the current selection. It is a faster way than using the following expression:
FILTER ( <granularity_table>, NOT ( ISBLANK ( <measure> ) ) )However, you might replace the entire CALCULATETABLE expression with just <granularity_table> if you want to consider blank values of the <measure> as 0.
The performance of the MedianX formula depends on the number of rows in the table iterated and on the complexity of the measure. If performance is bad, you might persist the <measure> result in a calculated column of the <table>, but this will remove the ability of applying filters to the median calculation at query time.
Excel has two different implementations of percentile calculation with three functions: PERCENTILE, PERCENTILE.INC, and PERCENTILE.EXC. They all return the Kth percentile of values, where K is in the range 0 to 1. The difference is that PERCENTILE and PERCENTILE.INC consider K as an inclusive range, while PERCENTILE.EXC considers the K range 0 to 1 as exclusive.
All of these functions and their DAX implementations receive a percentile value as parameter, which we call K.
The two DAX implementations of percentile require a few measures that are similar, but different enough to require two different set of formulas. The measures defined in each pattern are:
You need the ValueLow and ValueHigh measures in case the PercPos contains a decimal part, because then you have to interpolate between ValueLow and ValueHigh in order to return the correct percentile value.
Figure 4 shows an example of the calculations made with Excel and DAX formulas, using both algorithms of percentile (inclusive and exclusive).
In the following sections, the Percentile formulas execute the calculation on values stored in a table column, Data[Value], whereas the PercentileX formulas execute the calculation on values returned by a measure calculated at a given granularity.
The Percentile Inclusive implementation is the following.
K_Perc := <K> PercPos_Inc := ( CALCULATE ( COUNT ( Data[Value] ), ALLSELECTED ( Data[Value] ) ) – 1 ) * [K_Perc] ValueLow_Inc := MINX ( FILTER ( VALUES ( Data[Value] ), CALCULATE ( COUNT ( Data[Value] ), Data[Value] <= EARLIER ( Data[Value] ) ) >= ROUNDDOWN ( [PercPos_Inc], 0 ) + 1 ), Data[Value] ) ValueHigh_Inc := MINX ( FILTER ( VALUES ( Data[Value] ), CALCULATE ( COUNT ( Data[Value] ), Data[Value] <= EARLIER ( Data[Value] ) ) > ROUNDDOWN ( [PercPos_Inc], 0 ) + 1 ), Data[Value] ) Percentile_Inc := IF ( [K_Perc] >= 0 && [K_Perc] <= 1, [ValueLow_Inc] + ( [ValueHigh_Inc]  [ValueLow_Inc] ) * ( [PercPos_Inc]  ROUNDDOWN ( [PercPos_Inc], 0 ) ) )
The Percentile Exclusive implementation is the following.
K_Perc := <K> PercPos_Exc := ( CALCULATE ( COUNT ( Data[Value] ), ALLSELECTED ( Data[Value] ) ) + 1 ) * [K_Perc] ValueLow_Exc := MINX ( FILTER ( VALUES ( Data[Value] ), CALCULATE ( COUNT ( Data[Value] ), Data[Value] <= EARLIER ( Data[Value] ) ) >= ROUNDDOWN ( [PercPos_Exc], 0 ) ), Data[Value] ) ValueHigh_Exc := MINX ( FILTER ( VALUES ( Data[Value] ), CALCULATE ( COUNT ( Data[Value] ), Data[Value] <= EARLIER ( Data[Value] ) ) > ROUNDDOWN ( [PercPos_Exc], 0 ) ), Data[Value] ) Percentile_Exc := IF ( [K_Perc] > 0 && [K_Perc] < 1, [ValueLow_Exc] + ( [ValueHigh_Exc]  [ValueLow_Exc] ) * ( [PercPos_Exc]  ROUNDDOWN ( [PercPos_Exc], 0 ) ) )
The PercentileX Inclusive implementation is based on the following template, using these markers:
K_Perc := <K> PercPosX_Inc := ( CALCULATE ( COUNTROWS ( CALCULATETABLE ( <granularity_table>, <measure_table> ) ), ALLSELECTED ( <granularity_table> ) ) – 1 ) * [K_Perc] ValueLowX_Inc := MAXX ( TOPN ( ROUNDDOWN ( [PercPosX_Inc], 0 ) + 1, CALCULATETABLE ( <granularity_table>, <measure_table> ), <measure>, 1 ), <measure> ) ValueHighX_Inc := MAXX ( TOPN ( ROUNDUP ( [PercPosX_Inc], 0 ) + 1, CALCULATETABLE ( <granularity_table>, <measure_table> ), <measure>, 1 ), <measure> ) PercentileX_Inc := IF ( [K_Perc] >= 0 && [K_Perc] <= 1, [ValueLowX_Inc] + ( [ValueHighX_Inc]  [ValueLowX_Inc] ) * ( [PercPosX_Inc]  ROUNDDOWN ( [PercPosX_Inc], 0 ) ) )
For example, you can write the PercentileX_Inc of Total Amount of Sales for all the dates in the Date table as follows:
K_Perc := <K> PercPosX_Inc := ( CALCULATE ( COUNTROWS ( CALCULATETABLE ( 'Date', Sales ) ), ALLSELECTED ( 'Date' ) ) – 1 ) * [K_Perc] ValueLowX_Inc := MAXX ( TOPN ( ROUNDDOWN ( [PercPosX_Inc], 0 ) + 1, CALCULATETABLE ( 'Date', Sales ), [Total Amount], 1 ), [Total Amount] ) ValueHighX_Inc := MAXX ( TOPN ( ROUNDUP ( [PercPosX_Inc], 0 ) + 1, CALCULATETABLE ( 'Date', Sales ), [Total Amount], 1 ), [Total Amount] ) PercentileX_Inc := IF ( [K_Perc] >= 0 && [K_Perc] <= 1, [ValueLowX_Inc] + ( [ValueHighX_Inc]  [ValueLowX_Inc] ) * ( [PercPosX_Inc]  ROUNDDOWN ( [PercPosX_Inc], 0 ) ) )
The PercentileX Exclusive implementation is based on the following template, using the same markers used in PercentileX Inclusive:
K_Perc := <K> PercPosX_Exc := ( CALCULATE ( COUNTROWS ( CALCULATETABLE ( <granularity_table>, <measure_table> ) ), ALLSELECTED ( <granularity_table> ) ) + 1 ) * [K_Perc] ValueLowX_Exc := MAXX ( TOPN ( ROUNDDOWN ( [PercPosX_Exc], 0 ), CALCULATETABLE ( <granularity_table>, <measure_table> ), <measure>, 1 ), <measure> ) ValueHighX_Exc := MAXX ( TOPN ( ROUNDUP ( [PercPosX_Exc], 0 ), CALCULATETABLE ( <granularity_table>, <measure_table> ), <measure>, 1 ), <measure> ) PercentileX_Exc := IF ( [K_Perc] > 0 && [K_Perc] < 1, [ValueLowX_Exc] + ( [ValueHighX_Exc]  [ValueLowX_Exc] ) * ( [PercPosX_Exc]  ROUNDDOWN ( [PercPosX_Exc], 0 ) ) )
For example, you can write the PercentileX_Exc of Total Amount of Sales for all the dates in the Date table as follows:
K_Perc := <K> PercPosX_Exc := ( CALCULATE ( COUNTROWS ( CALCULATETABLE ( 'Date', Sales ) ), ALLSELECTED ( 'Date' ) ) + 1 ) * [K_Perc] ValueLowX_Exc := MAXX ( TOPN ( ROUNDDOWN ( [PercPosX_Exc], 0 ), CALCULATETABLE ( 'Date', Sales ), [Total Amount], 1 ), [Total Amount] ) ValueHighX_Exc := MAXX ( TOPN ( ROUNDUP ( [PercPosX_Exc], 0 ), CALCULATETABLE ( 'Date', Sales ), [Total Amount], 1 ), [Total Amount] ) PercentileX_Exc := IF ( [K_Perc] > 0 && [K_Perc] < 1, [ValueLowX_Exc] + ( [ValueHighX_Exc]  [ValueLowX_Exc] ) * ( [PercPosX_Exc]  ROUNDDOWN ( [PercPosX_Exc], 0 ) ) )]]>