This blog follows on from https://www.thedataschool.co.uk/john-power/creating-a-start-and-end-date-month-selectors in which Start Date and End Date parameters were created and used to view the SUM of Sales for a selected month range.
Creating a comparison range enables us to dynamically compare a measure over a selected number of months, with the previous same number of months. For example, if we are looking at the Sales figures for April 2019, May 2019 and June 2019, we would be able to see the difference compared to January 2019, February 2019 and March 2019.
This guide will also be using the US Superstore Date Set and some of the calculations mentioned here were created and explained in the previous blog.
The process will be broken down into 5 sections:
- Creating our End Month for Comparison Range
- Creating our Months Difference Between Start Date and End Date Chosen Range
- Creating our Start Month for Comparison Range
- Creating our Comparison Month Range Boolean
- Creating our Sales for Comparison Month Range calculation
Creating our End Month for Comparison Range
Firstly, we will need to work out the End Date Month for our comparison range (and call this 'End Month for Comparison Range'):
This calculation is using the DATEADD function to find our Comparison End Date by looking at our Start Date Parameter and subtracting 1 month. (A full explanation of DATEADD and the other main date functions can be found here - https://www.youtube.com/watch?v=DCGb7sczUWY).
If we drag this onto view, we can see that as our Start Date Parameter is set to 'April 2019', our 'End Month for Comparison Range' is correctly showing as 'March 2019':
Creating our Months Difference Between Start Date and End Date Chosen Range
In order to make this dynamic, we now need to create another calculation 'Months Difference Between Start Date and End Date Chosen Range' to identify the number of months difference between our Start and End Date Parameters:
If we also drag this onto view, we can see that this working as expected:
Creating our Start Month for Comparison Range
We can now use these calculations to create our 'Start Month for Comparison Range':
For this we will again use the DATEADD function and construct this to start at our 'End Month for Comparison Range' and look back however many months we have calculated in 'Months Difference Between Start Date and End Date Chosen Range'. If we also drag this onto view we can see that our calculation is working as expected:
Creating our Comparison Month Range Boolean
We are now able to create a Boolean using 'Start Month for Comparison Range' and 'End Month for Comparison Range' to identify the months within this range:
In plain English, this calculation is saying "If a month is smaller (or the same) as the End month and bigger (or the same) as our Start month, return True. If not, return False":
Creating our Sales for Comparison Month Range
We will now use this Boolean to only return the Sales for the months in our comparison range:
This calculation will only return the months showing as True for our 'Comparison Month Range Boolean':
Now that we have this figure we can incorporate it into any of our BANs. Please click on the below link for a follow up guide on how to do this:
https://www.thedataschool.co.uk/john-power/creating-a-comparison-range-based-off-of-chosen-month-range-2/
