This example aims to illustrate a model design that can compute the cumulative sum of 12 months based on a user-specified starting month and crossing year boundaries. Conceptually, this is similar to a PeriodsToDate use case. However, it supports dynamic starting months and can cross year boundaries.
Use Case
A user wants a server-side running total of the Internet Sales Amount (ISA) starting at a user-specified period and extending for 12 months. It is imperative that calculation span Year boundaries. Because of the dynamic Start Date and Year boundary requirements, we cannot use PeriodsToDate(). See Figure 1 for an illustration.
Figure 1: Power BI Cumulative Sum of Internet Sales Amount Starting from a Custom Start Date.
The Dynamic Lag Solution
A server-side MDX expression that uses PeriodsToDate() won’t work because this function is tightly coupled to Ancestor and Descendant members of a server-side hierarchy. As a result, the PeriodsToDate function start date can’t be parameterized, and its range can’t cross ancestor boundaries such as Year. To solve these problems, the better approach is to use a Range expression with a Dynamically computed Lag Value. Something like this:
1 SUM([DateCustom].[StandardMonth].CurrentMember.Lag([Measures].[DynamicLagFromSelected]): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
The approach is to dynamically compute the lag value based on the CurrentMember position. Don’t try running the above expression because Lag() doesn’t accept dynamically computed values, so we’ll have to work around that limitation. For now, let’s focus on the computation of the dynamic lag value 'DynamicLagFromSelected'
To compute ‘DynamicLagFromSelected' you’ll first need to define a dimension that the user can use to indicate on what period they would like to start their calculation. This is modeled below in a dimension called “MonthStartSelectorDim”. It is essential to point out that the sole purpose of this dimension is to collect input from the user and shouldn’t be related to any real Fact Tables or Dimensions in the model. You can see from the screenshot that the MonthStartSelectorDim is a Degenerate Dim from a QDS dataset that is simply selecting from the existing Calendar table. There is nothing special about choosing to use a QDS over a table, other approaches would work too. The main point is to show a list of YearMonths to the user in a Filter control.
Figure 2: Important parts of the ASAdventureSmall Model for this solution
Because the MonthStartSelectorDim isn’t related to any of the Fact tables backing real measures in the model, it is important to have UnrelatedDimensionHandling settings set for the measures used in the report set to “Show Empty Value”.
Now that we have a Dimension that the user can use to specify their desired start date and the models' real date dimension and hierarchy (See [DateCustom].[StandardMonth] in Figure 2) we can figure out a way to compute the difference in months, aka “Lag Periods”, between the two. From Figure 2 you can see that both the [DateCustom].[StandardMonth].[Month] and [MonthStartSelectorDim].[MonthStartSelectorHier].[MonthStartSelector] keys are of type Long. Since this is the case, we can just subtract the two key values to compute a Lag offset and apply a little extra logic to handle the boundary conditions.
Here is the expression to retrieve the [DateCustom].[StandardMonth] member key. The expression is called StandardMonthMemberKey:
1 [DateCustom].[StandardMonth].CurrentMember.MEMBER_KEYThe comparable expression for [MonthStartSelectorDim].[MonthStartSelectorHier] is called MonthSelectorKey:
1 [MonthStartSelectorDim].[MonthStartSelectorHier].CurrentMember.MEMBER_KEYThe expression that subtracts the two and handles the boundary conditions is called DynamicLagFromSelected:
1 CASE WHEN ([Measures].[StandardMonthMemberKey] - [Measures].[MonthSelectorKey]) < 0
2 THEN NULL
3 WHEN ([Measures].[StandardMonthMemberKey] - [Measures].[MonthSelectorKey]) > 88
4 THEN ([Measures].[StandardMonthMemberKey] - [Measures].[MonthSelectorKey]) - 88
5 ELSE [Measures].[StandardMonthMemberKey] - [Measures].[MonthSelectorKey] END
It is suggested to break the MDX expressions down into smaller expressions so that they can be displayed separately for debugging purposes. Here is what we have so far in Power BI:
Figure 3: Components of DynamicLagFromSelected
You can see that the StandardMonthMemberKey changes with each row and is a clear match for the Month column. The MonthSelectorKey doesn’t change because its value is held constant by the value selected by the user in the MonthStartSelector filter.
The DynamicLagFromSelected computes the difference between the key value that the user selects in the filter and the key used in the Month column. Due to the [Year][Month] concatenation scheme, the key values jump by a constant value of 88 when Year boundaries are crossed. The 'CASE WHEN' statement handles the boundary conditions so that a positive, constantly increasing Lag value is produced for each month following the user-selected filter value.
Finally, to work around the fact that Lag() can’t accept a variable, we have to brute-force the parameterization of the aggregated Range expression in the “Dynamic_SUM_ISA” expression like below:
1 CASE
2 WHEN [Measures].[DynamicLagFromSelected] = 0 THEN
3 SUM([DateCustom].[StandardMonth].CurrentMember.Lag(0): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
4 WHEN [Measures].[DynamicLagFromSelected] = 1 THEN
5 SUM([DateCustom].[StandardMonth].CurrentMember.Lag(1): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
6 WHEN [Measures].[DynamicLagFromSelected] = 2 THEN
7 SUM([DateCustom].[StandardMonth].CurrentMember.Lag(2): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
8 WHEN [Measures].[DynamicLagFromSelected] = 3 THEN
9 SUM([DateCustom].[StandardMonth].CurrentMember.Lag(3): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
10 WHEN [Measures].[DynamicLagFromSelected] = 4 THEN
11 SUM([DateCustom].[StandardMonth].CurrentMember.Lag(4): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
12 WHEN [Measures].[DynamicLagFromSelected] = 5 THEN
13 SUM([DateCustom].[StandardMonth].CurrentMember.Lag(5): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
14 WHEN [Measures].[DynamicLagFromSelected] = 6 THEN
15 SUM([DateCustom].[StandardMonth].CurrentMember.Lag(6): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
16 WHEN [Measures].[DynamicLagFromSelected] = 7 THEN
17 SUM([DateCustom].[StandardMonth].CurrentMember.Lag(7): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
18 WHEN [Measures].[DynamicLagFromSelected] = 8 THEN
19 SUM([DateCustom].[StandardMonth].CurrentMember.Lag(8): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
20 WHEN [Measures].[DynamicLagFromSelected] = 9 THEN
21 SUM([DateCustom].[StandardMonth].CurrentMember.Lag(9): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
22 WHEN [Measures].[DynamicLagFromSelected] = 10 THEN
23 SUM([DateCustom].[StandardMonth].CurrentMember.Lag(10): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
24 WHEN [Measures].[DynamicLagFromSelected] = 11 THEN
25 SUM([DateCustom].[StandardMonth].CurrentMember.Lag(11): [DateCustom].[StandardMonth].CurrentMember,[Measures].[Internet Sales Amount Local])
26 ELSE NULL ENDThe final result is displayed in Figure 1 above.
The AtScale model used for this article is: 'AdventureWorksDW2012Multidimensional-EE'