Moving averages with slicer reanimator

In 2013, Rob showed how to use a disconnected table and slicer to show moving averages with a variable time period. That post built on an earlier post, which steps through the process of creating moving averages.

What-If-Parameters, teased at MDIS in June 2017, have just been released to Power BI. This is a great new way to get disconnected tables into Power BI. But, it should also help more people than ever discover the joy of disconnected tables. With the latest update of Power BI, you can now CLICK A BUTTON to create new what-if-parameters. As a result, the button creates a series of numbers in a disconnected table and a harvester measure to get the selected value. As an added bonus, you can even add the slicer to the page at the same time.

So, could we use the what-if-parameters to get the same results? Let’s try it. To begin with, we’ll need a Sales and Calendar table and a DAX measure for Units Sold.

Step 1: Create a new What-if-ParameterWhat-if-parameter window with moving averages settings

From the Modeling tab, click the New Parameter button to bring up the  window. This window creates a table and a DAX harvester measure. You can also add a slicer to the page. Values for Minimum, Maximum, and Increment are required. As a great touch, there’s an optional AlternateResult. This default value is in effect if no slicers are active, or if the user clicks more than one slicer.

     = SELECTEDVALUE (Table[ColumnName, [AlternateResult] )

In a recent post, Matt Allington points out that this function is the same as:

     =IF(HASONEVALUE(Table[Column]), VALUES(Table[Column]), <alternate result>)

Step 2: View your new disconnected table and slicer
moving averages table and slicer

The new table now displays in the fields area. Before, we had to make these tables in Power Query, or with the Enter Data button in Power BI. But no more! Now, the What-if button creates a table for us using DAX. No query or pasting required. By the way, this new tool is  only available in Power BI. Neither the What-If-Parameter nor the DAX functions behind it are yet available in Excel.

The What-If-Parameter button created this tablegenerateseries table for moving averages slicer

Note the new GENERATESERIES() function!  This is how the table looks in Data view. There’s a table formula at the top and the results on the rows of the table.

     [MA Length] = GENERATESERIES(-12, 12, 1).

This formula plugs in the values from the window: start of -12, end of 12, and move up by 1. Increment can be any positive number like 2, 5, or .05.

     GENERATESERIES ( StartValue, EndValue, [IncrementValue] )

The formulas below are from Rob’s original post. I made a couple of changes. Firstly, I replaced the original harvester measure.

     [Variable Moving Sum] =
     VAR CurrentDate =
          IF (
               [MA Length Value] > 0,
               FIRSTDATE ( ‘Calendar'[Date] ),
               LASTDATE ( ‘Calendar'[Date] )
          )
     RETURN
     CALCULATE([Units Sold],
          DATESINPERIOD(Calendar[Date],
          CurrentDate,
          [MA Length Value],Month
          )          
     )
 
     [Variable Moving Average] =
     [Variable Moving Sum] /
          CALCULATE( DISTINCTCOUNT(Calendar[Year Month]),
               DATESINPERIOD(Calendar[Date],
               LASTDATE(Calendar[Date]),
               [MA Length Value], Month
          )
     )

Secondly, I set the first argument for DATESINPERIOD based on whether the range is forward or back. The original post advised this step to fix the time range for forward averaging.

Here’s the syntax for DATESINPERIOD():

     DATESINPERIOD(<dates>,<start_date>,<number_of_intervals>,<interval>)

How do we get the right number of months going forward?

DATESINPERIOD casts ahead or back x months, starting with the start date parameter. Going back, there’s no problem because it starts with the last date of the current month, and therefore includes the full current month. Going forward, the measure should begin with the first date of the current month in order to include the full month. Instead, if the last date is used for forward averaging, the period will include one day from the current month. And this makes the count of months one more than it should be! For example, with three months forward, the measure would divide by four instead of three. So, the formula for [Variable Moving Sum] here corrects this issue. Since our variable switches between FIRSTDATE() and LASTDATE(),  this gives us the right count of months.

After creating these measures, we can put [Variable Moving Average] on the chart. Then, we can apply color to the sales and moving average lines. In addition, we also have new formatting options: markers and line style.

Step 3: Create & format your moving average chartYoY Profit Change and Variable Moving Average by Month & Year chart

The AlternateValue of the harvester measure is -3. Thus, the moving average displays at three months back, even without a slicer selection. All great stuff!

Personally, I love these new What-if-parameters. And I especially love GENERATESERIES(). However, with fine tuning, we can go beyond what what’s possible out of the box. So stay tuned later this year for some cool stuff in Part 2!

Thanks to Reid Havens, for designing the moving averages dashboard. Download it below!

Download the Power BI Desktop (.pbix) Report Here

X

Get Your Files

  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 14 Comments

  1. Great article, love the the design, look and feel. A clean and simple way of presenting an often asked question. BTW, download works for me.

  2. Notes from user still early on the learning curve:
    I was at first expectingmeasures to be in the associated tables, Sales Measures to be in the Fact Sales. I don’t think I’ve seen putting the measures in a separate Table all together like that. While I like that, it appears it would give me some challenge in wanting to look at the data table for the fireld names. Just another mindset for me to adapt to.

    1. Brian: very sorry about that. Reid offered to do a dashboard for the post, and I didn’t update the article to reflect that. Using a dedicated measure table is more or less useful, depending on the situation. What’s nice is that it puts all the measures at the top, so that consumers of the report can find them without thinking about what table it’s from. Here’s a post showing how to create a measure table in Power Pivot. Maybe it’s time we re-animated that post for Power BI, along with a discussion of the pros and cons.

Leave a Comment or Question