Week 20 for #WOW2021, and Lorna Brown set the challenge based on comparing dates. The requirement was to avoid LoDs in preference of table calculations. Unintentionally, I managed to build a solution without table calcs either. This just happened based on the problem solving process I ended up taking. I figured I would encounter a need for a table calc as I progressed, but it didn’t happen.
- Defining the key calculated fields
- Building the BAN
- Building the Line Chart
- The Reset button
- Adding the dashboard interactivity
Defining the key calculated fields
To drive this viz, you’ll need 2 parameters – one to store the selected date and one for the selected comparison timeframe.
Set to be an integer (more efficient in calcs later), but display text on screen
We now need to work out what the comparison date will be for each of the selections.
We’ll start with the date last year. We’re looking for the ‘equivalent’ day of the year in the previous year. I didn’t read the hint provided, so came up with my own method based on the month and whether we’re in a leap year or not
Equivalent Day Last Year
DATE(IF ((DATEPART(‘month’,[pSelectedDate]) <=2) AND ((YEAR([pSelectedDate])-1)%4=0)) OR ((DATEPART(‘month’,[pSelectedDate])>=3) AND (YEAR([pSelectedDate])%4=0))
So what is this saying….
If the month of the pSelectedDate is Jan or Feb AND the previous year is a leap year (as it can be divided by 4 with no remainder), OR if the month of pSelectedDate is not Jan or Feb and this year is a leap year, then get the exact same date from last year, but add on 2 days, otherwise get the exact same date from last year, but add on just 1 day.
Now let’s consider the same day for last month. I played with Lorna’s solution, entering difference dates to see what the compare date came back as, and it wasn’t always clear to me what the logic was that was being used. So this is what I came up with. If the pSelectedDate was for example, the 3rd Tuesday in the month, then I wanted to get the 3rd Tuesday in the previous month. However, based on the length of months and when months start and end, some months can have nearly 5 weeks in a month, while the previous may only have 4. In my logic therefore, if there was no 5th Tuesday in the previous month, I would return the 4th Tuesday of the month. So in that instance if pSelectedDate is the 4th Tuesday in the month, I’d compare to the 4th Tuesday of the previous month. If pSelectedDate is moved to the 5th Tuesday in the same month, it would also compare to the 4th Tuesday of the previous month – PHEW! all a bit mind boggling perhaps, and it certainly took the most amount of time in this challenge.
I broke this down into multiple calculations.
DoW Selected Date
What is the day of the week number for the pSelectedDate? If pSelectedDate is 18th May 2021, this will be 3 (ie Tuesday).
Exact Date Prev Month
DATE(DATEADD(‘month’, -1, [pSelectedDate]))
as it says on the tin – exact same date last month, so if pSelectedDate is 18th May, this will be 18th April.
Prior DoW Prev Mth
DATE(CASE [DoW Selected Date]
WHEN 1 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Sunday’)
WHEN 2 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Monday’)
WHEN 3 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Tuesday’)
WHEN 4 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Wednesday’)
WHEN 5 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Thursday’)
WHEN 6 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Friday’)
WHEN 7 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Saturday’)
If pSelectedDate is a Tuesday, then this returns the date of the Tuesday that is before the exact date last month. If pSelectedDate is Tuesday 18th May, Exact Date Prev Month is 18th April, which is a Sunday. Truncating this date to the start of the week, where the week starts on a Tuesday, returns Tues 13th April.
Next DoW Prev Mth
DATE(DATEADD(‘week’, 1, [Prior DoW Prev Mth]))
Just adds 1 week onto the above.
Equivalent Day Last Mth
IF MONTH([pSelectedDate]) = MONTH([Next DoW Prev Month]) THEN [Prior DoW Prev Mth]
ELSE [Next DoW Prev Month]
If the months of pSelectedDate and New DoW Prev Mth are the same, then use the ‘previous’ date, otherwise use the ‘next date’. This took some pen and paper to work out!
So now having worked out what the dates might be, I just plugged them into the below to get the comparison date we need.
DATE(IF [pComparison] = 1 THEN [Equivalent Day Last Year]
ELSEIF [pComparison] = 2 THEN [Equivalent Day Last Month]
ELSE DATEADD(‘week’, -1, [pSelectedDate])
Further fields we then need are
Selected Date Sales
IF [Order Date] = [pSelectedDate] THEN [Sales] END
Compared Date Sales
IF [Order Date] = [Compare Date] THEN [Sales] END
(SUM([Selected Date Sales]) – SUM([Compared Date Sales])) / SUM([Compared Date Sales])
custom formatted to ▲0.0%;▼0.0%
Building the BAN
I built this using a bar mark type with MIN(1) on Columns and the axis fixed from 0 to 1.
Then all the relevant fields were added to the Label field and organised accordingly. All date fields were formatted to the <weekday>, dd mmmm, yyyy format.
The % Difference field was added to Colour, and then the colour legend formatted as below
Building the Line Chart
The line chart only needs to show the information between the Compare Date and the pSelectedDate.
Dates to Show
[Order Date]>=[Compare Date] AND [Order Date]<=[pSelectedDate]
This needs to be on Filter shelf and set to True.
The main line chart is then just simply Order Date (exact date, continuous) plotted against Sales
For the circle markers, we can add Selected Date Sales and Compared Date Sales as ‘shared measures’ onto another axis
To get the ‘label’ points to show, we’re going to plot 2 more marks at an arbitrary point on the axis, but at the dates we care about. For this I created
IF [Order Date] = [pSelectedDate] OR [Order Date]= [Compare Date] THEN -500 END
Add this into the Measure Values section, but aggregated to Avg rather than Sum
We want to label these marks, but not with the -500 value.
Selected | Compared Date Sales
IF [Order Date] = [pSelectedDate] THEN ([Selected Date Sales])
ELSEIF [Order Date] = [Compare Date] THEN ([Compared Date Sales])
Add this field onto the Label shelf of the Measure Values marks card and show mark labels
But we only want the labels to show against the lower marks. I have to admit I tried many things to try to make this work, but in the end had to peek at Lorna’s solution. The label setting needs to be – its the Measure Names field that is key here!
I then set the colour of the Minus 500 measure to white, so you can’t see it
The Reset Button
I created a Default Date field storing DATE(#2021-05-18#) and added to a sheet as below.
Adding the dashboard interactivity
On click of a point on the chart, it should set the pSelectedDate parameter. This is a parameter action.
Create fields True = True and False = False. Add these 2 fields to the Detail shelf on the line chart.
Then on the dashboard, add a dashboard filter action as below, setting the fields to map True to False.
Finally, the reset button needs to set the parameter too :
Hopefully this should be enough to help you complete this challenge. My published viz is here.
Happy vizzin’! Stay Safe!