Defining the calculations
The requirements were to ensure there were only 7 calculated fields used, and no date hardcoding (including in the title – a feature I missed to start with). So let’s start by just going through the required calculations.
We need to identify the latest year in the data set
This uses an LoD (Level of Detail) calculation to identify the maximum date in the whole data set, which is 31st Dec 2021, and then extracts the Year of this ie 2021.
From this, we work out
[Current Year] – 1
Both of these fields return numbers, so automatically sit in the measures section of the left hand data pane (ie under the horizontal line). I want to treat these as dimensions, so I just drag the fields above the line.
We now need to create dedicated fields to store the Sales values for both years
IF YEAR([Order Date])=[Current Year] THEN [Sales] END
IF YEAR([Order Date])=[Previous Year] THEN [Sales] END
and with both of these, we can work out the
SUM([CY Sales])-SUM([PY Sales])
[TIP] This is custom formatted to △#,##0;▽#,##0.
I googled ‘UTF 8 triangles’ and used this link to find the suitable shapes which I just copied and pasted into the number format field.
We’re going to need to determine whether the difference is positive or not.
This is another [TIP] making use of the array function. If the Difference is negative, it will return 0 as this is the maximum of the two numbers. I’m not entirely sure if this is more efficient than simply writing Difference<=0, but I wanted to incorporate another of the tips presented.
The final calculation we need is another of the PY Sales field, as we need another distinct Measure Name value to display. I simply chose to duplicate the existing field to have a PY Sales (copy) field.
Building the viz
Add Category to Columns, Segment to Rows and then add CY Sales to Columns, which will create a horizontal bar chart. Then drag PY Sales to the CY Sales axis, and when the ‘two columns’ icon appears, drop the field.
This will automatically change the pills so Measure Values is on Columns and Measure Names is on Rows.
Swap the order of the pills on the Measure Values section on the left hand side, so PY Sales is listed before CY Sales.
Add Measure Names to the Colour shelf and adjust. Increase the width of the rows.
Check the Show Mark Labels option on the Label shelf and adjust alignment to display the text to the left
Increase the Size of the bars to the maximum size, and add a white border (via option on Colour shelf)
Add PY Sales (Copy) to Columns, and change the mark type to Gantt Bar. Remove Measure Names from the Colour shelf of this marks card, as it will automatically have been added. Instead add the Is Loss? field to Colour and adjust.
Add Difference to the Size shelf, then click on Size, and reduce it to as small as possible. Set the border of this mark to Automatic (it should become a little thicker).
Next add the Difference field to the Label shelf, align right and set the font colour to match mark colour.
Now make the chart dual axis, synchronise axis, and set the mark type of the Measure Names mark type back to a bar.
On the All marks card, add CY Sales, PY Sales and Difference to the Tooltip shelf. And add Current Year and Previous Year to the Detail shelf.
Adjust the Tooltip against the All marks card, so it is the same when you hover on all of the marks. And edit the title of the chart, referencing the Current Year and Previous Year fields.
The challenge has a ‘space’ between each Segment, and this is the final TIP I used.
On the Measure Values section on the left below the marks card, type in MIN(NULL). This will initially create a new ‘blank’ row between the bars and the gantt marks, which isn’t where we want the blank row to be.
To resolve this, simply click on the MIN(NULL) text in the chart and drag the text below the PY Sales (copy) text
And now you just need to uncheck Show Header against the Measure Names pill on Rows, and the Measure Values and PY Sales (copy) fields on the Columns. Then remove all row and column borders and gridlines and hide labels for rows and columns.
Hopefully you’ve got the final viz which you can now add to a dashboard. My published version is here.
Happy vizzin’! Stay Safe!