2 Replies Latest reply on May 7, 2017 4:56 PM by Brad Blankenship

# I am setting up a KPI report with multiple measures, my data goes back to 2015 for the measures and I would like to create a 3 month rolling formula vs prior year 3 month rolling calculation?

This is an example of the report I am building.  I would like a 3 v 3 Calculated Column and a Goal column which is a stand alone number for each metric.  want it to look like this:

• ###### 1. Re: I am setting up a KPI report with multiple measures, my data goes back to 2015 for the measures and I would like to create a 3 month rolling formula vs prior year 3 month rolling calculation?

Not sure but find my approach as reference below and stored in attached workbook version 9.3 located in the original thread

0. Parameter defined based in date

1. Date -1: date(str(int(mid(str([Date Selector]),1,4))-1)+str(mid(str([Date Selector]),5,6)))    This calculated field will give you the selected day but one year back

2. Date -2: date(str(int(mid(str([Date Selector]),1,4))-1)+str(mid(str([Date Selector]),5,6)))    This calculated field will give you the selected day but two years back

3. Dispay??: DATEDIFF('month',[date],[Date Selector])>=0 and DATEDIFF('month',[date],[Date Selector])<=2

or

DATEDIFF('month',[date],[Date -1])>=0 and DATEDIFF('month',[date],[Date -1])<=2

or

DATEDIFF('month',[date],[Date -2])>=0 and DATEDIFF('month',[date],[Date -2])<=2

Regards,

Norbert

• ###### 2. Re: I am setting up a KPI report with multiple measures, my data goes back to 2015 for the measures and I would like to create a 3 month rolling formula vs prior year 3 month rolling calculation?

Thanks Norbert.  I tried to do this, but I am not sure it gives me what I am looking for.  In your screenshots is the "delta" metrics the difference of 3 month rolling vs the same 3 month rolling from the PY (for example:  for 3 v 3, I want the results of Jan 17, Feb 17, and March 17 rolled together vs Jan 16, Feb 16, and March 16 rolled together).  Also, I am looking to make the 3 v 3 a column like Year is so it would be next to 2017.