In this blog I will show you how to create a bar chart showing the current year of a measure (Sales) and then how to create Gantt lines overlaying the bar chart showing the figures from the year before.
In this example we are using data from Sample Superstore where we are comparing Sales figures between 2020 and 2021.
First we want to create a calculated fields for the Previous and Current year. For this, select the down arrow on the left sided Data pane and select ‘Create Calculated Field…’. Once this pane opens follow the picture below and write the expressions which captures the previous and current years from using the LAST command. The LAST function produces a measure which is a count from the last row in the data to the first. When this is equal to 0 it signifies the latest row in the dataset and in this instance the current year (2021). For the previous year we equal this to 1 (2020). If we decided to update the dataset which included an extra year (2022) then the graph would be able to pick this up without intervention and show this against the previous year.
Once you have created these fields then we can start adding our dimensions and measures to the Columns and Rows. To begin you will want to hold right click and drag ‘Order Date’ to the columns area. When the pop up appears you should select the MONTH in the blue (discrete) category.
Simultaneously we should also be dragging over our Current Year and Previous Year calculated fields onto the Rows area. And then right clicking onto either of the green pills and selecting dual axis. The columns/rows area should now look like this:
Next we are going to drag our Order Date field onto the Color under the ‘All’ Marks pane. This is give the years in the final graph different colors to help us distinguish.
Now we want to use table calculations to create data in our window. On both the Current Year and Previous Year you must right click, Compute using and select Order Date as shown in the image. Also you will need to right click on either vertical axis and select Synchronize Axis. A dual line axis graph should now be showing.
Finally, the last step is to select Bar and Gantt Bar on the Current Year and Previous Year tabs on the Marks Card.
The outcome should hopefully look something similar to the Bar Chart at the beginning of this blog.