Do you want to build a simple Sales dashboard?

This week’s challenge was the first challenge to be set by guest author Meera Umasnakar, and is mainly a formatting/layout challenge.

The requirement was to show some month to date / year to date metrics in comparison to the previous month to date, and also work out where the current month might finish.

I took up the challenge on Weds 25th March, and the challenge was dated ‘as at 24th March’. There was nothing in the requirements to indicate whether this was ‘hardcoded’ to this date, or whether it happened to be this date based on the time I viewed (ie it was yesterday, the last full day). In the interest of being ‘flexible’ I therefore chose to design my solution more dynamically. I based my challenge on viewing the data up to ‘yesterday’, where ‘yesterday’ is yesterday’s date in 2019. So if today is 27 March 2020, then the dashboard will be based up to 26 March 2019. Hope that’s clear.

As a result of this, there’s a fair few date calculations involved, so let’s crack on.

Setting up the calculations

I like to build up my calculations so they’re easier to read, rather than nest everything. First up we need

Current Year

{MAX(YEAR([Order Date]))}

The maximum year in the data set (which happens to be 2019 for the data source I’m connected to).

Yesterday

MAKEDATE([Current Year],MONTH(TODAY()),DAY(TODAY()))-1

Fake the date based on today’s date (see discussion above).

Current Month

DATETRUNC(‘month’, [Yesterday])

returns yesterday’s date to be 1st of the month eg 26th March 2019 becomes 1st March 2019

Current MTD Sales

IF DATETRUNC(‘month’,[Order Date]) = [Current Month]
AND [Order Date]<=[Yesterday] THEN [Sales] END

If the order date is between the 1st of the month and yesterday, then capture the Sales value.

We can repeat for profit

Current MTD Profit

IF DATETRUNC(‘month’,[Order Date]) = [Current Month]
AND [Order Date]<=[Yesterday] THEN [Profit] END

Current YTD Sales

IF YEAR([Order Date]) = [Current Year] AND [Order Date]<=[Yesterday] THEN[Sales] END

If the order date is within the latest year, but also less than today, then capture the Sales value.

Again repeat for profit

Current YTD Profit

IF YEAR([Order Date]) = [Current Year] AND [Order Date]<=[Yesterday] THEN[Profit] END

Format all of these to $ with 1 decimal place.

To work out the numbers we need for the previous month, we need some further dates

Yesterday Previous Month

DATEADD(‘month’,-1,[Yesterday])

Just takes 1 month off of yesterday’s date.

Previous Month

DATETRUNC(‘month’, [Yesterday Previous Month])

gets back to the 1st of the previous month’s date.

Previous MTD Sales

IF DATETRUNC(‘month’,[Order Date]) = [Previous Month]
AND [Order Date]<=[Yesterday Previous Month] THEN [Sales] END

If the order date is within the previous month, but only up to the equivalent day of the month (previous month to date), then capture the Sales value.

Previous MTD Profit

IF DATETRUNC(‘month’,[Order Date]) = [Previous Month]
AND [Order Date]<=[Yesterday Previous Month] THEN [Profit] END

Format both of these to $ and 1 decimal place

MoM Sales

SUM([Current MTD Sales])-SUM([Previous MTD Sales])

Month on Month Sales, is just the difference between the two variables.

Similarly for profit

MoM Profit

SUM([Current MTD Profit])-SUM([Previous MTD Profit])

Both these need custom formatting applied

▲”$”#,##0,.0K;▼”$”#,##0,.0K

To get this, use the formatting to set to $ and 1 decimal place, then once set, change the format to ‘custom formatting’, which will display the ‘formatting code’. Then add the ▲▼ symbols. I use this site to get the characters I need.

The final calculation we need to work out is what’s been referred to as the ‘run rate’. This is basically trying to show what the final month sales/profit will be, based on the current rate. This means taking the current MTD Sales/Profit, dividing it by the number days this has been computed over to get an average sales/profit per day. Then this number is multiplied by the number of days in the month. Got it?

So we need to start by working out….

Days in Current MTD

DAY([Yesterday])

This just returns the number of the day.

Days in Current Full Month

DATEDIFF(‘day’, DATETRUNC(‘month’, [Yesterday]), DATEADD(‘month’,1,DATETRUNC(‘month’, [Yesterday])))

This is counting the number of days between the 1st of the month (based on yesterday), and the 1st of the next month.

Run Rate Sales

(SUM([Current MTD Sales])/SUM([Days in Current MTD])) * SUM([Days in Current Full Month])

Run Rate Profit

(SUM([Current MTD Profit])/SUM([Days in Current MTD])) * SUM([Days in Current Full Month])

Format these to $ and 1 decimal place.

This gives us all of the core calculations we need to build the KPIs.

Building the KPI card

This is done on one sheet, and is simply utilising the Text mark type.

By typing in, create a pill MIN(0) on the Rows shelf, and another one right next to it. Change the mark type to Text. This gives you 2 ‘cards’ you can now use. Add the all Sales related calculated fields to the Text shelf of the first card, and the Profit fields to the second card, and just format the font/layout accordingly. Then remove the axis headers, gridlines etc etc.

Building the Sales YoY Bar Chart

To ensure we only have the dates for the last 2 years, up to the current point in time, we need some additional fields

Previous Year

[Current Year]-1

Dates To Include

YEAR([Order Date])>= [Previous Year] AND [Order Date]<= [Yesterday]

Add Dates To Include to the Filter shelf and set to True.

Now build the viz by

  • Sales on Rows
  • Order Date on Columns, set to the Month level only (discrete blue pill).
  • Mark Type = bar
  • Order Date on Colour, set to Year level. Adjust colours to suit
  • Order Date on Size, set to Year level. Adjust size to suit so the latest year is narrower.
  • Set Stack Marks = Off (Analysis -> Stack Marks). This will stop the bars for each year from sitting on top of each other.
  • Format the Order Date axis, so the Month is displayed as 1st letter only.
  • Format the Sales axis so the value is displayed to K with no decimal places.
  • Due to the above, the format of the Sales value on the tooltip is likely to change too. If this happens, duplicate the Sales field, rename it to Tooltip – Sales or similar and format to $ with 0 dp. Add this to the Tooltip shelf.
  • You’ll need to do similar to get a month field for the tooltip. Create a calculated field Tooltip-Month = DATETRUNC(‘month’,[Order Date]) and custom format this to mmm yy. Add this to the Tooltip shelf.

Repeat the same steps to build the YoY Profit chart.

Date Sheet

The final dashboard indicates the date of the report. As my dashboard is dynamic and changes based on the current date, I couldn’t hardcode this. So I built the date to display on another sheet. This means I have 1 more sheet than stated in the challenge.

I simply added Yesterday to the Text shelf and referenced it

Building the Dashboard

Here we get into a bit of container fun! As I did last week, I’ll try to just step through the order you need to place the objects on the dashboard…

  • Add a Text object to store the title.
  • Beneath it, add the Date sheet.
  • To the right of both of these, add another Text object to store the sheet information text which will be displayed top right.
  • Add a Horizontal container beneath all of the above. Set the background of this container to light grey, and Inner Padding to 10 all round
  • Add the KPI sheet into the container and remove the title. Adjust the height of the objects to suit.
  • Now add a Vertical container to the right of the KPI chart. Adjust width to suit
  • Add the Sales YoY chart to the vertical container. Set the background of this object to white, so the title background isn’t grey.
  • Add the Profit YoY sheet below the Sales one. Again set the background of this to white.
  • Remove the container on the right hand side that contains the legends.
  • Add a floating blank object to the sheet. Set the background of this to light grey, and then adjust the positioning and height and width so it’s splitting your KPI card.
  • Finally if you haven’t already, edit the title and summary text appropriately.

And that should be it! My published viz is here.

Happy vizzin’!

Donna

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s