**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-Parameter*

**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*

* *

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

**N****ote 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 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

Great dashboard.

I can’t download the file, tough.

Now it works. Thanks.

Great article Fred!

Thanks, Micheal! I hope to see you soon.

Great article… but, cannot download file, can you verify is working?

The pbix file does not can be downloaded

Excellent! Very practical and applicable information. Thank you Fred!

Great article, but like others, I cannot get the download to work?

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.

Download Works in Chrome

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.

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.

https://powerpivotpro.com/2013/02/organizing-your-measures-with-a-dummy-table-guest-post-from-eric-hutton/

If link doesn’t come through, you can search the site for “organizing your measures with a dummy table”

Great catch, I failed to have some of the screenshots update to that. Here’s also a more recent post of mine that shows how to create that measures table in Power BI Desktop as well. As Fred said it definitely has it’s uses, and it’s a personal practice of mine to include that in my reports. https://powerpivotpro.com/2017/06/top-5-power-bi-visual-design-practices-transforming-good-great/

To get correct results CurrentDate has to be taken into account twice (numerator and denominator):

VariableMovingAverage =

VAR CurrentDate =

IF (

[Moving Average Value] > 0;

FIRSTDATE ( ‘Calendar'[Date] );

LASTDATE ( ‘Calendar'[Date] )

)

VAR VariableMovingSum =

CALCULATE (

[YoY Profit Change];

DATESINPERIOD ( ‘Calendar'[Date]; CurrentDate; [Moving Average Value]; MONTH )

)

RETURN

[Variable Moving Sum]

/ CALCULATE (

DISTINCTCOUNT ( ‘Calendar'[Calendar Month & Year] );

DATESINPERIOD (

‘Calendar'[Date];

CurrentDate;

[Moving Average Value];

MONTH

)

)

VariableMovingAverage =

VAR CurrentDate =

IF (

[Moving Average Value] > 0;

FIRSTDATE ( ‘Calendar'[Date] );

LASTDATE ( ‘Calendar'[Date] )

)

VAR VariableMovingSum =

CALCULATE (

[YoY Profit Change];

DATESINPERIOD ( ‘Calendar'[Date]; CurrentDate; [Moving Average Value]; MONTH )

)

RETURN

VariableMovingSum –ADJUSTED

/ CALCULATE (

DISTINCTCOUNT ( ‘Calendar'[Calendar Month & Year] );

DATESINPERIOD (

‘Calendar'[Date];

CurrentDate;

[Moving Average Value];

MONTH

)

)

Frank,

You are right. Here’s how I handled it in the pbix embedded in Part 2: https://powerpivotpro.com/2017/11/dax-moving-averages-slicer-gfitw/

Variable Moving Average =

VAR NumberOfMonths =

CALCULATE (

DISTINCTCOUNT ( Calendar[YearMonth] ),

DATESINPERIOD (

Calendar[Date],

IF (

[MA Length Value] < 0,

ENDOFMONTH ( LASTDATE ( Calendar[Date] ) ),

STARTOFMONTH ( FIRSTDATE ( Calendar[Date] ) )

),

[MA Length Value],

MONTH

)

)

RETURN

IF (

[MA Length Value] = 0,

[Total Sales],

[Variable Moving Sum] / NumberOfMonths

)