Calculate Moving Average Last 6 Month with not sequencial Date

[andrehawari]

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 !

[v-piga-msft]

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.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[Zubair_Muhammad · Accepted solution]

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

[Zubair_Muhammad]

@andrehawari

See attached file as well