7 Replies Latest reply on Jan 30, 2019 5:41 AM by Zhouyi Zhang

# Year Over Year Rolling 12 Months Filter Calculation

Hello All,

I have a Snapshot Date Parameter that displays an end of month snapshot date. I have filter calculations that will display either Current Year or Rolling 12 Month view of performance metrics for my organization based on the Snapshot Date Parameter.

I want to be able to display CY Rolling 12 Months and PY Rolling 12 Months in the same visualization for year-over-year comparison. I was able to create the following filter calculation to get the CY Rolling 12 Months, but, again, I need assistance in creating a PY Rolling 12 Months and a YOY Rolling 12 Months Calculation.

[Snapshot Date] > dateadd('month',-12,date([Snapshot Date Parameter])) and [Snapshot Date] <= date([Snapshot Date Parameter])

Candace

• ###### 1. Re: Year Over Year Rolling 12 Months Filter Calculation

Hi, Candace

Can you provide a sample workbook and your expected figure based on your sample data?

ZZ

• ###### 2. Re: Year Over Year Rolling 12 Months Filter Calculation

Using superstore, and is this what you want?

Thanks,

Shin

• ###### 3. Re: Year Over Year Rolling 12 Months Filter Calculation

Hi Zhouyi,

Attached is a sample workbook. The current view shows the year over year comparison using CY/YTD (in bars) and PY (averaged and represented via a reference line). I want to replicate this same viz, but using PY Rolling 12 Months and CY Rolling 12 Months as a comparison rather than the traditional CY/YTD vs PY.

Thanks,

Candace

• ###### 4. Re: Year Over Year Rolling 12 Months Filter Calculation

Hi Shinichiro,

Thank you for the detailed feedback. This is close to what I am looking for, but not exactly. Based on your example, how can I display the Rolling P 12 as a reference line with it being a dimension and not a measure?

• ###### 5. Re: Year Over Year Rolling 12 Months Filter Calculation

Hi, Candace

Below is the calculation field I created

And drag the rolling 12 month calculation fields to detail and add reference as below

Make sure remove your original YOY date filter from filter.

Hope this helps

ZZ

• ###### 6. Re: Year Over Year Rolling 12 Months Filter Calculation

Hi Zhouyi,

This is perfect! The only thing is the months do not fall into the correct order when I adjust the Snapshot Date Parameter using the Months Date Part format. In theory, if the Snapshot Date Parameter displays "11/30/2018" the dates in the view should be Dec 2017 - Nov 2018 in that order; however, the view displays it as Jan 2018 - Dec 2017, which can be misleading and confusing for our end users. Any ideas on how to remedy this?

Snapshot Date Format - Months (Date Part)

Snapshot Date Format - Months (Date Value)

• ###### 7. Re: Year Over Year Rolling 12 Months Filter Calculation

Hi, Candace

See my workaround attached.

Hope this helps

ZZ