How to display the average total count for these 12 months?

[Anonymous]

My detail as below:

Dimension:

=[Date Created.autoCalendar.YearMonth]

Measure:

=Count({$ <Temp={'Task_17'}>*$<[Date Created.autoCalendar.YearMonth]={">=$(=AddMonths(MonthStart(Today()-20),-11))"}> } [SR No])

Subtitle:

=Count({$ <Temp={'Task_17'}>*$<[Date Created.autoCalendar.YearMonth]={">=$(=AddMonths(MonthStart(Today()-20),-11))"}> } [SR No])

*My subtitle show 3483. But what i want is 496/12=41.33. Please advise.

[kaushiknsolanki]

Try this.

Sum(Aggr(Count({$ <Temp={'Task_17'}>*$<[Date Created.autoCalendar.YearMonth]={">=$(=AddMonths(MonthStart(Today()-20),-11))"}> } [SR No]),[Date Created.autoCalendar.YearMonth]))/12

Regards,

Kaushik Solanki

[Anonymous]

Hi Kaushik,

By using your formula i get the average as 42.25. i think it include the null value.

i try to exclude them using this formula but it is not working.

My formula:

Sum(Aggr(Count({$ <Temp={'Task_17'}>*$<[SR No]-={"''"}>

*$<[Date Created.autoCalendar.YearMonth]={">=$(=AddMonths(MonthStart(Today()-20),-11))"}> }

[SR No]),[Date Created.autoCalendar.YearMonth]))/12

Please help. Thanks.

[kaushiknsolanki]

Hi,

Can you share the sample data.

Regards,

Kaushik Solanki