Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi @nlucia ,
Can you try:
count( { < date_submited = {">=$(=Monthstart(AddMonths(Today(),-12))) <= $(=Monthend(AddMonths(Today(),-1)))"} > } opportunities ) / 12
thanks @ggijben .
this does not works. I keep trying
Can you provide the value of the Dimension and Measure used in your bar chart?
This is the Dimension
and the Measure
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
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
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... 😪
can you post the exact expression you used?
and a screenshot of the entire expression editor