Power BI Tip: Use a Scatter Chart to Create a Calendar Report

The Scatter Chart in Power BI and Excel is very useful chart for visualizing three different metrics in tandem. But with a little bit of work you can use a Scatter Chart to create a Calendar chart for visualizing your metrics across the days of an individual month.

To configure a Scatter Chart too mimic a Calendar type report, you need the following:

1) An attribute for the day number of the week (1,2,3,4,5,6,7).
2) An attribute for the day number of the month (1,2,3…29,30,31).
2) An attribute for the week number of the month (1,2,3,4,5,6).
3) An attribute for sorting the week numbers in reverse order.
4) A business metric you wish to represent in the report.

Most of these items you can get from a traditional date dimension. In this example, I’m utilizing the Adventure Works DW database which has a date dimension table.

To set up the Scatter chart correctly, configure the visualization as seen in this screen shot from Power BI Desktop.

I want to point out a couple things here. First, you can optionally add a field to the Legend to differentiate between the weekend and weekdays or to identify holidays, as seen below.

Secondly, in order to display the Calendar in the correct order, we actually need to reverse the order to the Week numbers so that the first week of the month is numerically higher than the last week of the month. To do this I used a TSQL Case statement to populate the new column in the Adventure Works Date dimension table:

[WeekNumberOfMonth]  AS 

    ((datepart(week,[FullDateAlternateKey])

    -datepart(week,dateadd(month,datediff(month,(0),[FullDateAlternateKey]),(0))))+(1)),

[WeekNumberOfMonthReverse]  AS 

    (case (datepart(week,[FullDateAlternateKey])-datepart(week,dateadd(month,datediff(month,(0),[FullDateAlternateKey]),(0))))+(1) 

        when (1) then (6) 

        when (2) then (5) 

        when (3) then (4) 

        when (4) then (3) 

        when (5) then (2) 

        when (6) then (1)  

    end)

In the above code snippet, I also included the TSQL I used to create the WeekNumberOfMonth column.

You may also notice that I included the days of the week names at the bottom of the chart. I kind of cheated here as the Scatter chart doesn’t allow string fields on the X axis. I simply used a text box to rig this up but when the charts resize it shifts out of position, so be aware of this.

The final step is to hide all the axes and the background if you desire. In my case I left the data labels visible to display the day of the month number.

Resources

Interested in other Power BI tricks? Check these out:

Import Excel Power View Reports into Power BI
Clean Your Power Query Code
Create Cool Slicers with Treemaps, Funnels, Column & Bar Charts