How to write an expression for below requirement

[kunkumnaveen]

Hello All,

I need to design a bar graph with Dimension (B_Mode)and with a expression which accumulate last three months  avg(Count_Br ) excluding the selected month 

For Example :i have max data till 2019 Oct.

if no month and year is selected it has to  then take the avg of past three months(Sep,Avg,Jul)

if any month is selected (2019,Feb) then it should take avg of  the past three months(2019Jan,2018Dec,2018,Nov)..

Chart:Bar Chart

Dimension: B_Mode

Expression: Avg({   ?   }Count_Br)

Thank

Please find the attached sample data...

[kunkumnaveen]

Hi  Thanks a lot man its working ,

just need a small suggestion from you, i am trying to do sum of count_BR of three past months and then avg of that sum of past three month.

i tried below below expression but having some miss fire .

avg(
{<Year,M_Month, Date = {">=$(=monthstart(addmonths(max(Date),-3)))<=$(=monthend(addmonths(max(Date),-1)))"}>}
aggr(Sum({<Year,M_Month, Date = {">=$(=monthstart(addmonths(max(Date),-3)))<=$(=monthend(addmonths(max(Date),-1)))"}>}Count_Br),B_Mode))

what am i missing 

[Kushal_Chawda · Accepted solution]

this should work

=alt(avg({<Year,M_Month, Date = {">=$(=monthstart(addmonths(max(Date),-3)))<=$(=monthend(addmonths(max(Date),-1)))"}>}
aggr(sum({<Year,M_Month, Date = {">=$(=monthstart(addmonths(max(Date),-3)))<=$(=monthend(addmonths(max(Date),-1)))"}>}Count_Br), 
Year, M_Month)
),0)