cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calculate Moving Average Last 6 Month with not sequencial Date

Hi,  I need to create measure that calculate moving average for the last 6 months, but the date is not always sequencial

for instance, In the Table below

Scenario 1: If I choose Jun 2018, the measure will display value average last 6 months, but with calculate from column that value is "Has Sales=Yes". That is (Oct 2017 + Nov 2017 + Dec 2017 + Jan 2018 + Mar 2018 + May 2018) / 6 = (19+20+21+22+24+26)/6

Scenario 2: If I choose March 2018, the measure will display value average last 6 months, but with calculate from column that value is "Has Sales=Yes". That is (Jul 2017 + Sep 2017 + Oct 2017 + Nov 2017 + Dec 2017+ Jan 2018) / 6 = (16+18+19+20+21+22)/6

 Month Has Sales Value Jan-17 Yes 10 Feb-17 Yes 11 Mar-17 No 12 Apr-17 No 13 May-17 Yes 14 Jun-17 Yes 15 Jul-17 Yes 16 Aug-17 No 17 Sep-17 Yes 18 Oct-17 Yes 19 Nov-17 Yes 20 Dec-17 Yes 21 Jan-18 Yes 22 Feb-18 No 23 Mar-18 Yes 24 Apr-18 No 25 May-18 Yes 26 Jun-18 Yes 27

I found the solution in this link

https://community.powerbi.com/t5/Desktop/Average-of-last-6-months/td-p/128738

But This wont work in my case because the Datesinperiod function in above links only give statics last N months

Any idea would be really appreciated

Thanks !

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculate Moving Average Last 6 Month with not sequencial Date

@andrehawari

Try this MEAASURE

```Measure =
VAR mydate =
SELECTEDVALUE ( Table1[Month] )
VAR Previous6 =
TOPN (
6,
FILTER ( ALL ( Table1 ), Table1[Month] < mydate && Table1[Has Sales] = "Yes" ),
[Month], DESC
)
RETURN
AVERAGEX ( Previous6, [Value] )```
3 REPLIES 3
Super User

## Re: Calculate Moving Average Last 6 Month with not sequencial Date

@andrehawari

Try this MEAASURE

```Measure =
VAR mydate =
SELECTEDVALUE ( Table1[Month] )
VAR Previous6 =
TOPN (
6,
FILTER ( ALL ( Table1 ), Table1[Month] < mydate && Table1[Has Sales] = "Yes" ),
[Month], DESC
)
RETURN
AVERAGEX ( Previous6, [Value] )```
Super User

## Re: Calculate Moving Average Last 6 Month with not sequencial Date

@andrehawari

See attached file as well

Community Support Team

## Re: Calculate Moving Average Last 6 Month with not sequencial Date

Hi @andrehawari,

By my test, the measure from @Zubair_Muhammad should be the solution.

If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 59 members 730 guests
Recent signins: