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

[andrehawari]

hi I have similar problem like this

http://community.powerbi.com/t5/Desktop/Calculate-Moving-Average-Last-6-Month-with-not-sequencial-Da....

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

But the structure table is more complex, because it includes category and sub category. The current solution does not give the abiity to slice by those 2 columns

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

Any idea would be really appreaciated

Thanks!

[Ashish_Mathur]

Hi,

What result are you expecting?

[v-juanli-msft · Accepted solution]

Hi @andrehawari

Try this measure

Measure = 
VAR mydate =
 SELECTEDVALUE ( Sheet1[Month] ) 
VAR cate=SELECTEDVALUE(Sheet1[Category]) 
VAR subcate=SELECTEDVALUE(Sheet1[SubCategory])
VAR Previous6 =
 TOPN (
 6,
 FILTER ( ALL ( Sheet1 ), Sheet1[Month] < mydate && Sheet1[Has Sales] = "Yes"&&[Category]=cate&&[SubCategory]=subcate ),
 [Month], DESC
 )
RETURN
 AVERAGEX ( Previous6, [Value] )

Best Regards

Maggie

[andrehawari]

Hi Maggies,

thanks a lot ! It works now, however, I was wondering, since my data will have have millions of row in azure analysis service database, will it affect performance badly using the averagex function? 

thanks

[v-juanli-msft]

Hi @andrehawari

Have you use the formula as a test?

Does it perform well?

If not, please tell me, I may change other formula which could perform better.

Best Regards

Maggie

[andrehawari]

The system perform well on the sample data,  I will get back to you once the system is live in production because it will contains million of rows after that