During my last project I was asked to create a moving 12 months sum of revenue for each month and then compare the sum to the moving 12m sum of the same month one year earlier for each of the displayed last 12 months and display a % change between those values.
The operation as described below but for each of the last12 months:
I can see how a client found that comparison useful, however I haven’t seen it before and had to reach out for some TIL core team consultant help to help me out with a tricky table calcs to get there (thanks Lorna!).
The part I was missing was adding discrete Month and Year to detail. Here’s the solution step by step on Superstore example:
First, drag your measure to rows and continuous month to columns.Change marks to bar chart.
Right click and add a table calculation to the measure: Moving calculation, sum of the previous 11 periods for Specific Dimensions – in this case just month.
Duplicate the measure pill on the rows by pressing ctrl and dragging it next to one another.
Click on the second measure we have just created so that it opens it’s card in Marks and drag a discrete year and month to detail.
Edit the table calculation for the second pill. Check all Specific dimensions for the primary calculation. Check Add secondary calculation % of difference. In Specific Dimensions check the box of Year and Month (If you are unsure which month to select: one of them will return a blank axis, so just select the other one that returns the data).
Change the Mark type to line – note this will create dots rather than a line.
Create a calculated field: Last 12 months ‘LAST()<=11’ and drag it on Columns. Edit the table calculation to make sure it is computed using all Specific Dimensions at the deepest level. Drag the calculation to the view again this time drop it on Path in the Marks – this will create a line
Last steps are: 1. hiding the previous months – right click on ‘False’ header and click ‘Hide’.
2. Right click on the measure to create a dual-axis. Bring the % axis to the front if needed by right-clicking on it. Remove the measure names from colors on the second Sales mark card. Go to the Bar marks card click on the size and adjust the bar width manually.
The chart is now ready!
It’s worth to point one thing: Because we compute the calculations on discrete year the line will break between the calendar years. On the chart above we happen to look at Jan – Dec data so this is not visible, but if I filter out most recent month you can see it on the image below. I’d say it’s a fairly aesthetic low price for achieving the end goal, but if you have ideas on how to fix this please let me know in comments.
Thanks for reading