DAX Reanimator: Moving Averages Controlled by Slicer (Part 2, including GFITW)

Today, I’ll show you how to bring the awesome to moving averages with DAX text formulas for slicers and titles…  and yes, I’ll throw in a Greatest Formula In the World (GFITW) version, too. In Part One, I used What-if-parameters in Power BI to control the moving average period with a slicer. And the original post from 2013 is Moving Averages Controlled by a Slicer, which brought the awesome to Moving Averages, Sums, Etc.

Last time, I promised to go beyond what can be done right out of the box using What-if-parameters. I’m going to help you take your reports from ordinary to awesome by using features like:

• Small sets of values to choose from:  -12, -6, -5, -4, -3, -2, 2, 3, 4, 5, 6, 12.  By itself, the GENERATESERIES() function can only make sets that follow a fixed pattern: +1, +.05, +2, etc.
• Moving average slicers with text labels, but what-if-parameters only generate numbers.
• Dynamically generated title for the chart.

But wait, there’s more! I also added some bonus items:

• Viewing multiple parameters at the same time. AND…
• The Greatest Formula in the World (GFITW) version of the moving average formulas. For those who use specialized calendars for manufacturing or retail, like a 4-4-5 calendar.
  

A Small Set of Values to Choose From

The first thing I noticed about GENERATESERIES() is that it’s a table function. It returns a table instead of just one value. That means we can go straight to the New Table button on the Modeling tab. This button lets us create a new table in the data model from a table formula.

The New Table button is handy for quickly adding a simple calendar table to a model. For example, the table created with this formula below is trimmed to the Order Date column of the Sales table. Note that the calendar starts on the first day of the month, because otherwise it could throw off the time intelligence functions.

Calendar =
CALENDAR (
    DATE ( YEAR ( MIN ( Sales[OrderDate] ) ), MONTH ( MIN ( Sales[OrderDate] ) ), 1 ),
    MAX ( Sales[OrderDate] )
)

And New Table is also a great place to look at table functions used in formulas, for troubleshooting, or for learning DAX.

Here is a formula to generate a table made up on non-regular intervals. Tables are held in variables at the top of the formula (Yes! Tables in variables!). I use UNION to mash up the different series into one MA Length table. Matt Allington blogged this approach when GENERATESERIES was introduced.

This result is a table that’s not entirely continuous, but the order doesn’t matter because the numbers will be sorted automatically.

Moving Average Slicer with Text Labels

Moving Average Label, sorted by length value

Next, we add the text for the table. The text for the rows comes from a calculated column we added to the Moving Average Length tableby clicking the New Column button on the Modeling tab and entering this formula:

Months to Include =
VAR MonthText =
    FORMAT ( ABS ( ‘MA Length'[MA Length] ), “General Number” )
VAR S_Text =
    IF ( ABS ( ‘MA Length'[MA Length] ) = 1, “”, “s” )
RETURN
    SWITCH (
        TRUE (),
‘MA Length'[MA Length] > 0, MonthText & ” Month”
& S_Text
& ” Forward”,
‘MA Length'[MA Length] < 0, MonthText & ” Month”
& S_Text
& ” Back”,
‘MA Length'[MA Length] = 0, “Current Month”
    )

This applies our diabolical old friend Switch TRUE(), and also includes a test for a value of 1 for values that are singular. When creating a text equivalent for a value, be sure to set the sort by column in the Modeling tab so that it doesn’t come out alphabetical.

A Dynamically Generated Title

This makes a text label for the top of the report. I put it onto the report in a using a Card visualization. Make sure to set the sort by column to sort the calculated column by the MA Length value.

Chart Title =
IF (
    COUNTROWS ( ‘Moving Average Length’ ) > 1,
    “Monthly Sales vs. 3 Months Back Moving Average “,
    IF (
        COUNTROWS ( ‘Moving Average Length’ ) = 1
&& [MA Length Value] = 0,
        “Monthly Sales”,
        “Monthly Sales vs. “
& FIRSTNONBLANK (
‘Moving Average Length'[Months to Include],
[Variable Moving Average]
            )
& ” Moving Average”
    )
)

Text formulas are also great for static titles:

Chart Title2 = “Actual Sales vs. Multiple Moving Average Periods”

Why? Because they have more formatting options and you can also make them match dynamic titles.

BONUS 1: Multiple Moving Averages

Because we used disconnected tables, we can compare multiple months back and forward at the same time by putting ‘MA Length'[Months to Include] column on the legend of the line chart. The slicer is still to the left of the chart above, so we don’t have to see all the ranges at the same time. In the first version, we compare different measures: Total Sales and a dynamically set moving average period. In this version, however, we comparing different values of the disconnected table. This is why I have 0/Current Month in my series: so that it could be compared with multiple moving averages. Our SWITCH() statement just substitutes Total Sales for the moving average calculation when the Current Month is active.

Bonus #2: GFITW Moving Averages

This version of the measures have the same results, but are used when an organization uses a custom calendar, like a retail 4-4-5 calendar. The formula depends on having a column in the Calendar table which is an index of all months, where Period 13 of Fiscal Year of one year is one less than Period 1 of the following year. Year + Month won’t work because it has gaps. My year month index is [Year] * 12 + [Month], which only works if your calendar always has exactly 12 months. The Power Pivot and Power BI book gives a great synonym for GFITW formulas: “Clear Filters, then Re-Filter.”

GFITW Variable Moving Sum =
VAR End_Month =
    IF (
[MA Length Value] > 0,
        MIN ( ‘Calendar'[YearMonth Index] ) + [MA Length Value]
– 1,
        MIN ( ‘Calendar'[YearMonth Index] )
    )
VAR Start_Month =
    IF (
[MA Length Value] < 0,
        MIN ( ‘Calendar'[YearMonth Index] ) + [MA Length Value]
+ 1,
        MIN ( ‘Calendar'[YearMonth Index] )
    )
RETURN
    CALCULATE (
[Sales Amount],
        FILTER (
            ALL ( ‘Calendar’ ),
‘Calendar'[YearMonth Index] >= Start_Month
&& ‘Calendar'[YearMonth Index] <= End_Month
        )
    )

I redefine the variables in both formulas instead of using measures, because it was the easiest way to avoid a filter context transition (formula breaking because of the measures being evaluated within the FILTER statement).  And let me tell you, if you really want to appreciate the convenience of time intelligence functions, try writing some GFITW versions of things!

GFITW Variable Moving Avg =
VAR End_Month =
    IF (
[MA Length Value] > 0,
        MIN ( ‘Calendar'[YearMonth Index] ) + [MA Length Value]
– 1,
        MIN ( ‘Calendar'[YearMonth Index] )
    )
VAR Start_Month =
    IF (
[MA Length Value] < 0,
        MIN ( ‘Calendar'[YearMonth Index] ) + [MA Length Value]
+ 1,
        MIN ( ‘Calendar'[YearMonth Index] )
    )
VAR NumberOfMonths =
    CALCULATE (
        DISTINCTCOUNT ( Calendar[YearMonth] ),
        FILTER (
            ALL ( ‘Calendar’ ),
‘Calendar'[YearMonth Index] >= Start_Month
&& ‘Calendar'[YearMonth Index] <= End_Month
        )
    )
RETURN
    IF (
[MA Length Value] = 0,
[Sales Amount],
[GFITW Variable Moving Sum] / NumberOfMonths
    )

Filters section under Visualizations: Page Level and Report Level

And as a finishing touch, I add some page filters to the report in Power BI. On the first page, I set MA Length <>0, so that the Current Month won’t display in the slicer.  And, on both moving average charts, I set visual level filters to show only completed months.

The report below follows (mostly) Reid’s Top 5 Design Tips for Power BI.