Compare monthly sales with average sales of last 90 days from that month

[Atinder]

Hello all, 

I want to create a report that shows sales growth up and down for each item compare with average of last 90 days or 3 months from that month. show me growth rate by %. 

Below is the example I want to achieve in the powerbi report. Automatically do the math and give %. 

Item_No	January	FEB	MARCH	APRIL	MAY
Apple	500	600	300	200	250
Calculations		(600-500)/500 =20%	1. (500+600)/2 =550   2. (300-550)/550  =-45%	1. (500+600+300)/3 = 466.7                    2.(200-466.7)/466.7 = -57%	1. (600+300+200)/3=275                       2. (250-275)/275= -9%
Sales Growth		20%	-45%	-57%	-9%

1. Is it possible to days?

2. is there any another way to achieve this result?

Thank you,

Paul

[v-zhangti · Accepted solution]

Hi @Atinder ,

You can try the following methods.

Cumulative 3 month = 
Var N1=SUMMARIZE(FILTER(ALL('Table'),[Date]<SELECTEDVALUE('Table'[Date])),[Date],"Sum",SUM('Table'[Sales]))
Var N2=TOPN(3,N1,[Date],DESC)
Var Cumulative3month=SUMX(N2,[Sum])
return
Cumulative3month

Count = 
Var N1=CALCULATE(COUNT('Table'[Date]),FILTER(ALL('Table'),[Date]<SELECTEDVALUE('Table'[Date])))
Return
IF(N1<=3,N1,3)

Sales Growth = 
Var N1=DIVIDE([Cumulative 3 month],[Count])
return
DIVIDE(SUM('Table'[Sales])-N1,N1)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.