Analytical Need
Sometimes we wish to see how we are performing in the last x months and comparing that to the previous parallel period.
For example, we wish to see how we perform from Sep last year to Aug this year and compare it with Sep 2 years ago to Aug last year.
Modeling Challenge
If we use the period over period mechanism, we will have the period number (even if displayed by text) ordered from the first one, not necessarily ending in the current period.
Solution
In our example we present a view of 12 months back.
We will create the widget with the following definitions:
- The x axis is the month (out of the date field)
- We will filter the x axis for the past 12 months like so:
Pick Time Frame and then 2 months.
Image 1. Date filter with 2 months selection
Click on Advanced and change the count to 12 and offset to 0 :
Image 2. Date filter with 12 months selection (including this month)
Click OK.
- Add 2 values:
- This Year - sum([Value]) - the regular formula you wish to calculate
- Last Year - (sum([Value]), prev([Months in date],12)) - same formula but with a measured value that uses the PREV function which gives you the option to take 12 months back for each of the dimension (Month) values.
You will get this widget:
Image 3. YoY but with a specific x Axis
But the x axis is misleading because of the year. So we would want to change it to reflect the month name like so:
Image 4. Formatting the x axis labels
And now the X axis will look like this:
Image 5. Final Widget
