cancel
Showing results for
Did you mean:
Regular Visitor

## 12 Month Rolling average.

Hi,

I have been asked to show our monthly sales with a moving average in a table (and chart). Here is some sample data:

 Sale Date Sale Amount 2/07/2017 \$15,000 11/07/2017 \$20,000 19/07/2017 \$4,000 21/07/2017 \$4,000 21/07/2017 \$60,000 2/08/2017 \$21,000 3/08/2017 \$15,000 19/08/2017 \$5,500 19/08/2017 \$15,000 29/08/2017 \$20,000 29/08/2017 \$4,000 1/09/2017 \$10,000 15/09/2017 \$15,000 17/09/2017 \$5,500 18/09/2017 \$15,000 25/09/2017 \$20,000 2/10/2017 \$4,000 5/10/2017 \$60,000 12/10/2017 \$21,000 26/10/2017 \$8,000 28/10/2017 \$11,000 28/10/2017 \$500 28/10/2017 \$15,000

It is simple to display the monthly totals, but I am stuck getting the rolling average to work. I have tried messing with a number of DAX expressions that solved similar problem on the forums but can't find exactly what I need.

This is the result I am after:

 Sale Month Total Monthly Sales 12 Month Moving Average Jul-17 \$103,000 Aug-17 \$80,500 Sep-17 \$65,500 Oct-17 \$119,500

Thanks,

Cliff

1 ACCEPTED SOLUTION
Resident Rockstar

Here is the solution,

Step 1: Create a quick measure -> Rolling average

Step 2: Edit the DAX of the quick measure -> Change AVERAGEX to SUMX

That's it

16 REPLIES 16
Frequent Visitor

Hi All,

I don't see 'Rolling average' in Quick measure. Is it removed/deprecated?

--

Yours

New Member

I do the rolling average and the result is ok. But when I graph the measure in a timeline, it shows datapoints into the future. E.g., let's say my data ends on 4/1/19 and I calculate a 10-day rolling average. The timeline goes up to 4/11/19, i.e. 10 days after my last data point. How can I make my graph end on 04/01/2019?

I would really appreciate help on this!

Anonymous
Not applicable

Hi @jmf2244

This is 10 days rolling average I used on one of my reports and it works well according to your issue.

kindly find the example attached and please let me know if it works:

Revenue rolling average =
CALCULATE(IF([Revenue]<>BLANK();

VAR __LAST_DATE = LASTDATE('Date'[Date].[Date])
RETURN
AVERAGEX(
DATESBETWEEN(
'Date'[Date].[Date];
);
CALCULATE([Revenue])
)
))
New Member

Thanks @Anonymous . However, I am getting the following error: "A single value for column 'Revenue' in table 'xxxx' cannot be determined." (I replaced "Revenue" and "xxxx" with the real names to follow your example)

It seems like IF needs a single value in the logical test, not a table, right?

Anonymous
Not applicable
New Member

I do the rolling average and the result is ok. But when I graph the measure in a timeline, it shows datapoints into the future. E.g., let's say my data ends on 4/1/19 and I calculate a 10-day rolling average. The timeline goes up to 4/11/19, i.e. 10 days after my last data point. How can I make my graph end on 04/01/2019?

I would really appreciate help on this!

Anonymous
Not applicable

hi, i have been using quick measures to calculate rolling average of the last 3,6,12 and 18 months to generate the rankings at work, for some odd reason the averages dont work anymore since we are in a new year, can someone help i am stuck

Resident Rockstar

Anonymous
Not applicable

i have sent you a link to my pbix file via private message

kind regards

HiltonM

Resident Rockstar

Try this and let me if it's working or not,

Sales Value L12M =

VAR  __EndDate = EOMONTH(LASTDATE(‘Sales’[Date]),0)

VAR  __StartDate = DATE(YEAR(__EndDate),MONTH(__EndDate) - 12,1)

RETURN

CALCULATE(SUM(‘Sales’[Value]), DATESBETWEEN(‘Sales’ [Date], __StartDate, __EndDate))

Regular Visitor

Hi SivaMani,

That is just repeating the value from the 'Total Monthly Sales' Column.

Resident Rockstar

If you create this as a calculated column, it repeats the value.

Please, create as a calculated measure.

Regular Visitor

Yes, I have created it as a measure.

Here is the result I get on the sample data:

Resident Rockstar

Here is the solution,

Step 1: Create a quick measure -> Rolling average

Step 2: Edit the DAX of the quick measure -> Change AVERAGEX to SUMX

That's it

Helper III

This is what i need thanks. Trying to get this to ignore an external slicer for geography though - i'm guessing i'm missing and ALL somewhere or something?

Regular Visitor

Hi SaviMani,

Thanks that did the trick. Although leaving it as AVERAGEX gives me that result I am after.

I do run a "Can't display the vidual." error when I try to apply date slice to the page and I have sent off a 'frown' to Microsoft support for this.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors