One of the common problems that clients are seeking to solve is a way to easily compare values from two different periods against each other. They want the ability to compare the same period between two years, a period against an immediately preceding period and current year to date compared to the previous year to date. They will also want to be able to change the length of this period at will, as well.

Problem

• We need a way to choose easily if we want to use today’s date or a custom date as a starting point and a way to choose this custom date.
• We need a way to select the length of these periods.
• We need to select what type of analysis we are going to do (Rolling Period, Year Over Year, Year to Date)

Selecting Today’s Date or a Custom Date

We will create a parameter with two options. This way, we can pass our decision to use today’s date or a custom date to another field:

 

Next we create another parameter so that if we do opt to use a custom date from the above selection, we can further interact and choose what date we want to use as a starting point:

 

We combine the above two selections into a single calculated field. Using our two parameters and this one calculated field, we can then pass our starting date into the below calculations:

 

Length of the Periods

One of our goals is to allow our end-user to select what type of time period they want to use and of course the length or how many of these periods to use. We create two parameters to handle this. In the first parameter, by using the singular day, week, month, etc. as our actual value in the left-hand column, we can more readily write calculated fields:

 

 

Now we’ve built the basic framework of the tools, we will be using in actual “hard” calculation. We are able to change the length of our periods, what type of period we want to use and what date we want to base our periods on.

Changing the Type of Analysis to Use

In this section, we will break down each of the period analysis calculations. In order to change our type of calculations, we created another parameter that will allow us to pick the type of period comparison we are attempting to do.

 

Year over Year

 

WHEN “YoY” THEN

//The first statement sets the conditions for a date to be considered the “selected period.” It will compare every date in our view to the date we’ve chosen as our starting point.

//It must be based on whatever date we’ve chosen and go back a user chosen number of months, weeks, days, etc.

//If the difference between these dates is >=0 and < our Period Length, we consider it to be the “selected period.”

IF DATEDIFF([Period Type],[Date],[DateComparisonToUse]) >=0 AND DATEDIFF([Period Type],[Date],[DateComparisonToUse])< [Period Length] THEN 0

//The second statement sets the conditions for a date to be considered the “previous period.” It will compare every date in our view to the date we’ve chosen as our starting point.

//It will check with the exact same methodology as the above statement, except it will remove a year from our starting point.

ELSEIF DATEDIFF([Period Type],[Date],DATEADD(‘year’,-1,[DateComparisonToUse])) >=0 AND DATEDIFF([Period Type],[Date],DATEADD(‘year’,-1,[DateComparisonToUse])) < [Period Length] THEN -1

END

Rolling Period

 

WHEN “RP” THEN

//The first statement sets the conditions for a date to be considered the “selected period.” It will check every date to the date we’ve chosen as our starting point.

//It must be based on whatever date we’ve chosen and go back a user chosen number of months, weeks, days, etc.

//If the difference between these dates is >=0 and < our Period Length, we consider it to be the “selected period.”

IF DATEDIFF([Period Type],[Date],[DateComparisonToUse]) >=0 AND DATEDIFF([Period Type],[Date],[DateComparisonToUse])< [Period Length] THEN 0

//The second statement sets the conditions for a date to be considered the “previous period.” It will compare every date to the date we’ve chosen as our starting point.

//It will be based on whatever date we’ve chosen and it will immediately precede the “selected period” and be the same length.

//If the difference between the dates is > the Period Length but also < two times the length, it will be the “previous period.”

ELSEIF DATEDIFF([Period Type],[Date],[DateComparisonToUse]) >= [Period Length] AND DATEDIFF([Period Type],[Date],[DateComparisonToUse]) <2*[Period Length] THEN -1

END

Year to Date

 

WHEN “YTD” THEN

//We check every date in our view and see it would come before our selected date in a calendar year

//If it does then we pass it to the case statement below if not we exit out and it is null

IF DATEPART(‘dayofyear’,[Date]) <= DATEPART('dayofyear',[DateComparisonToUse]) THEN

// The case statement determines if the date is in the same year or the previous year. Any other years are null.

CASE YEAR([Date]) – YEAR([DateComparisonToUse])

WHEN 0 THEN 0

WHEN -1 THEN -1

END

END

Putting It All Together

Now we are able to quickly change out comparison periods by using a combination of our various parameters (Analysis Type, Period Type, Period Length, Date Chooser and Custom Date) as these feed into our calculations that do the hard work for us. I’ve aliased 0 as “Selected,” -1 as “Previous” and NULL as “Not Being Used.” To see the final, interactive results of work, check out the Tableau viz below: