PowerPivotPro

PowerPivotPro is Coming to Phoenix

February 20 - 22, 2018

Registration for 2018 Public Training is now open!

AVAILABLE CLASSES

**Use the discount code “3ORMORE” when signing up 3 or more people.

FEBRUARY 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Kellan Danielson

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work! Two Days in our class and you are EMPOWERED!

  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • Taught by Kellan Danielson – PowerPivotPro Partner and Vice President of Client Services
  • You don’t need to be an IT professional – most of our students come from an Excel background

FEBRUARY 20 - 21

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

The Advanced DAX Course was such a hit in the first half of 2017 that we’ve expanded the course to 2 days!

Overview

  • This advanced DAX training class is taught completely in Power BI Desktop.
  • Students are encouraged to take our Foundations course and have hands on experience with the DAX language.
  • Taught by Ryan Sullivan – Principal Consultant.
  • Class material drawn from usage of Advanced DAX applications while consulting with hundreds of international firms.

FEBRUARY 22

Level Up Series: Power Query for Excel & Power BI

Instructor: Krissy Dyess

The second class in the series is our Level Up Series is Power Query for Excel & Power BI.

  • Students are encouraged to take our Foundations course and have hands on experience with Power Query in Excel or Power BI Desktop.
  • Taught by Krissy Dyess – PowerPivotPro Principal Consultant and Phoenix native!
  • We will cover common to unique business challenges made easy with Power Query’s data wrangling capabilities.
  • Intermediate to Advanced Level Power Query best practices distilled into easy to understand patterns to apply to your most common business challenges.
PowerPivotPro Logo

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

Guess that the "New Table" button creates?
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

DAX text formula column sorted by MA Length Column

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

Picture1

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

dax multiple moving averages slicer gfitw-Filters
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!
X

Subscribe

Fred Kaffenberger

A teacher at heart, Fred loves that moment when someone struggling to solve a problem makes a breakthrough. Fred spent six years in client services and sales for an online work order system. After this, he used Excel to help streamline commercial real estate operations for a major telecommunications carrier, where he discovered Power Pivot and Power BI. He was thrilled at how these tools helped him work smarter and more systematically. As an English major, Fred knows that a knack for working with data can surface in surprising places. 

This Post Has 9 Comments

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

  2. 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

    1. 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).

    1. 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.

  3. 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

Leave a Comment or Question