12 Month Rolling average.

[Cliff09]

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

[JLKM]

Hi All,

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

--

Yours

[jmf2244]

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]

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];

                DATEADD(__LAST_DATE; -10; DAY);

                DATEADD(__LAST_DATE; 1; DAY)

            );

            CALCULATE([Revenue])

        )

))

[jmf2244]

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]

Hi @jmf2244 

try referring to the article as attached:https://community.powerbi.com/t5/Desktop/Rolling-Average-Quick-Measure-Incorrectly-Calculating-the-F...

[jmf2244]

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]

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

[SivaMani]

Can you share more detail about your issue with sample data?

[Anonymous]

Hi @SivaMani

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

kind regards

HiltonM

[SivaMani]

@Cliff09,

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

[Cliff09]

Hi SivaMani,

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

[SivaMani]

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

Please, create as a calculated measure.

[Cliff09]

Yes, I have created it as a measure.

Here is the result I get on the sample data:

[SivaMani · Accepted solution]

@Cliff09,

Apologise for the late response. Addressed your issue.

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

[GlassShark1]

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?

[Cliff09]

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.

Thanks for your help!