Cumulative Number on KPI

[Anonymous]

Hello Community,

I have a measures created as variables

let vConvenienceIndex1 = 'If(sum(msrConvenienceIndexQuestionPoints1) > 0, sum(msrConvenienceIndexIndexScore1) / sum(msrConvenienceIndexQuestionPoints1), 0) ';

let vTreatmentIndex1 = 'If(sum(msrTreatmentIndexQuestionPoints1) > 0, sum(msrTreatmentIndexIndexScore1) / sum(msrTreatmentIndexQuestionPoints1), 0)';

let vValueIndex1 = 'If(sum(msrValueIndexQuestionPoints1) > 0, sum(msrValueIndexIndexScore1) / sum(msrValueIndexQuestionPoints1), 0)';

let vQualityIndex1 = 'If(sum(msrQualityIndexQuestionPoints1) > 0, sum(msrQualityIndexIndexScore1) / sum(msrQualityIndexQuestionPoints1), 0)';

and then one measure is the sum of these variable

let vCSE = '$(vConvenienceIndex1)  + $(vTreatmentIndex1)  + $(vValueIndex1) + $(vQualityIndex1)' ;

What I am trying to do is create a KPI for the last measure vCSE but based on cumulative calculation for each month.

For example when I select Jan 2017 it gives result for Jan only, but if I select Feb 2017 (without selecting Jan 2017) it gives me cumulative result for Jan and Feb.

I tried to do it with rangesum but it did not work.

Any ideas please?

Thanks

[Anonymous]

HI Omar,

One last question related to the same issue.

If I want to add the same calculation to the chart with MonthYear dimension, but I need it to display only last 3 months based on Month Year selection. For example if i select Sep 2017 the chart will show July, August September,

I tried something like below but it still shows all the Months Would you be able to help me with this one too?

Thanks:

if(sum({<SurveyNewDate={">$(monthend(AddMonths(Max(SurveyNewDate),-3)))"}>}(msrConvenienceIndexQuestionPoints1))>0,

sum({<SurveyNewDate={">$(monthend(AddMonths(Max(SurveyNewDate),-3)))"}>}(msrConvenienceIndexIndexScore1))/

sum({<SurveyNewDate={">$(monthend(AddMonths(Max(SurveyNewDate),-3)))"}>}(msrConvenienceIndexQuestionPoints1)),0)

[OmarBenSalem]

Maybe use a variable as follow:

vCumul3Months = date=,Year=,Month=,Quarter=, date={">=$(=addMonths(max(date),-3))<=$(=Max(date))"}

[Anonymous]

Great,

Thank you