Count last 12 month KPI

[nlucia]

Hello Everybody!

I need help with this KPI.

I have field [date_submitted.autoCalendar.Month] 

And I need to count Quantity of opportunities Opened the last 12 month. 

Total last 12 month= 1680 opportunities 

then I need to calculate the average:

1680/12 = Avg 140 opportunities

something Like that??

=count( { < date_submited = {"Month(AddMonths(Today(),-12)) "} > } opportunities )   /12

Thaks for your Hep!

[ggijben]

Hi @nlucia , 

Can you try:

count( { < date_submited = {">=$(=Monthstart(AddMonths(Today(),-12))) <=  $(=Monthend(AddMonths(Today(),-1)))"} > } opportunities )    /    12

[nlucia]

thanks @ggijben . 

this does not works. I keep trying

[ggijben]

Can you provide the value of the Dimension and Measure used in your bar chart?

[nlucia]

This is  the Dimension 

and the Measure 

[ggijben]

Can you try

count(distinct { < date_submited = {">=$(=Monthstart(AddMonths(Today(),-12))) <=  $(=Monthend(AddMonths(Today(),-1)))"} > } OpprId )    /    12

Or else:

SUM( AGGR( count(distinct { < date_submited = {">=$(=Monthstart(AddMonths(Today(),-12))) <=  $(=Monthend(AddMonths(Today(),-1)))"} > } OpprId ), [date_submitted.autoCalendar.YearMonth]))    /    12

[vinieme12]

You need to compare date_submitted with date values and not Month values

=count( { < date_submited = {">=$(=Monthstart(Max([date_submitted.autoCalendar.DATEFIELD] ),-12))"} > } opportunities )  

replace DATEFIELD with actual datefield name

[nlucia]

I tried but it doesn't work and I don't know why. From what I searched on the internet it seems to be the solution... 😪

[vinieme12]

can you post the exact expression you used?

and a screenshot of the entire expression editor