cancel
Showing results for
Did you mean:
Helper II

## 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 III

## 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] )```
Try my new Power BI game Cross the River
3 REPLIES 3
Super User III

## 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] )```
Try my new Power BI game Cross the River
Super User III

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

@andrehawari

See attached file as well

Try my new Power BI game Cross the River
Resident Rockstar

## 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.