To view the business performance, especially the financial numbers, sometimes people need to see the numbers for some overlapping periods, like the Last 6 Months, Last 1 Year, and Total Period to Date so that users can compare the performance over these periods.
See the table above as an example (Sample-Superstore Data). The reason why I don’t just do 3 separate tables is the users may need to just simply click the sort button to sort the table ascendingly/descendingly by one column, so then the whole table will be sorted, but 3 separate tables cannot be sorted at the same time by one column in one of 3 tables. So yeah, that’s pretty much why I want to share this small trick, hope it can help someone who happens to need it.
1. Create 2 sets from Order Date, which are “In last 6 months” and “In last 1 year”.
2. Create calculations for the Profit, Sales, and Quantity on “In last 6 months” and “In last 1 year”. See below for the calculations of “In last 1 year”, the same process for “In last 6 months”.
3. Put Measure Names and Sub-Categories into columns and Rows then the table will show up.
Done! so now we have the values of different periods on one table. We achieved the initial goal but it’s not perfect because there is no header for each period, the headers are separated by the columns, so it’s not that intuitive to see which period the values are on. I’ve done this table before but the end-user spotted the limits and he asked for a header for each period. Luckily I worked it out with Method 2 below.
1. Put the set “In last 1 year” into columns first, then put “In last 6 months” after, then put Measure Names in. Also put the Measure Names into the filter and filter on the measures you want, this time they’re “Profit”, “Sales”, and “Quantity”. For the Rows, put in the dimensions you want, in this case, it’s “Sub-Category”. Also, put the measure values into Text. Then the following table will come up.
What we can see from the headers it’s there are 2 layers of headers, the first layer of the header is In/Out of Last 1 year, and the second layer of headers is “In Last 1 year and In Last 6 months”, and “In Last 1 year but Out of Last 6 months” and “Out of Last 1 year and Out of Last 6 months”. So from this table, we can get the values for “In last 6 months” which are circled in red.
Notice that for the set in the Columns, right-click and select “Show in/Out of Set”.
2. Then in order to get the values for “In last 1 year” and “Total Period to Date”, we need to bring up Subtotals to the view. Click Analysis and in Totals, select “Show Row Grand Totals” and “Add All Subtotals”.
3. Then you’ll get the table below and get the values for “In Last 1 Year” in the left circle and “Total Period” in the right circle. ( Better to have tables for each period in other sheets to ensure you select the correct values) 4. Then select and hide the parts you don’t need.
5. Then you can hide the unnecessary Field label for columns or the headers.
6. Final step, rename the headers. To rename the totals, right-click select format and change the Label to the left.
To rename the header, right-click and select Edit Alias.
Finally, you’ll get the view. All done!