**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** table** **by 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.**

**Subscribe to PowerPivotPro!**

Very nice description Fred! I always look forward to the content that all of you put out at P3. Very informative and enlightening. Thanks.

Fred, when I use the GenerateSeries function instead of a table with incremented values per your or Matt’s examples, a single value is being returned. “MA length = GENERATESERIES(1,2,1)” as example is returning a single row with a value of 3 for me, possibly additive summation. The Union of the Variables per your example, returns a value of 0 for me.

Version: 2.51.4885.841 64-bit (October 2017) of Desktop being used.

Possibly broken in subsequent releases, or am I perhaps missing something?

Thanks for any thought you may have,

Thor

That is a really strange result. As of today, I’m on the November version thanks to the Office Store. The only way that I can reproduce a similar result is to drop the value into a table on the Report view (where it would automatically aggregate the value).

Thank you, yes my issue was looking on the reports versus the data panel, works exactly as expected. Sorry, still early in my experience with PowerQuery/PowerBI.

Excellent Post sir, amazing you are, very informative…

How do you calculate months forward?

Thanks, Muhammad. The difference between months forward and months back is that months forward uses the first date of the current month plus the next months; months back uses the last date of the current month plus the previous months.

Thank you Fred! This looks great but really complex. I hope to apply it soon.

Hi Fred,

It is a great and very interesting blogpost. You have really used some very useful techniques here.

I followed you all the way until I applied the slicer. When I select a value from my slicer I have no reaction at all – but when I select the value i generated using SelectedValue everything works fine.

As you can see below my forecast_Volume refers to the What_If_1 Value, but I expected that the chosen slicer Would use the value that is derived from the selected value in the slicer.

Where did you loose me?

What_If_1 Value = SELECTEDVALUE(What_If_1[What_If_1])

Forecast_Volume = CALCULATE(SUM(FactSales[Vol])*1;FILTER(FactSales;FactSales[VersionKey]=What_If_1[What_If_1 Value]))

Jørgen

Jørgen, are you using Power BI or Power Pivot?

I am using Power BI.