Thanks for responding am probably just not being clear I need to keep the KPI's.
I want to be able to carry out different calculations based on the KPI column.
I want to be able to check for KPI then do a calculations based on this.
for example in the same calculated field.
IF [KPI] = 'KPI 1 Average' Then Average if dates match etc........
IF [KPI] = "KPI 2 Sum' Then Sum ifs dates match etc....
This makes more sense to me now.
Do you know why the average function showing a different result to when I use the sum function divided by the number of months?
Thanks for your help today
Ned further investigation but ,I guess that is based on granularity level.
Avg is working on line item basis, and if you want to calc avg of (sum of month), formula should be sum of month / number of month.
If your expected result is sum / count of months, you'd better use it.
I used datepart with my parameter date work fine.
It looks like the avg function dividing by the full count which is 21 no matter how many values/months
I use the datepart function just now but be interested to know if you can use the avg function.
You can use "avg" function I believe, but that requires much much more complicated formula in each calculated field.
The reason is LOD calculation is captureing all the period of last year months + this year 9 month =21.
Limiting those period to only 9 month with LOD formula is quite complicated. (Not impossible, but not worth spending time for me)