ct covid trends part 2 - % changes over time, time intelligence, and date hierarchies in dax and power bi

In Part I, we took daily updates from Connecticut’s reported numbers around covid-19 - new cases, deaths, change in hospitalizations, and new tests administered and looked at some trends. There’s other fun exercises we can do with this data and given that we have daily data for 6 months and counting, let’s summarize some of this data and looked at the percent monthly changes.

You can view the report at the bottom of the page, or keep reading as we break down what’s going on here!

Time intelligence calculations can be a bit tricky to figure out sometimes. There’s an important nuance to this - your date dimension needs to be continuous (daily) and range from the earliest to the latest date in your model. That also means that there needs to be date and record for everyday - if there are blanks (for example, no cases for a given day), that record still needs to have a date and show a zero.

Calendar / Date Dimension Table

Lucky for us, the daily data I’ve been capturing subscribes to this format. But what if it doesn’t? As a best practice, it’s a good idea to always create a separate date table to use for time intelligence calculations. It’s really easy. To automatically generate a date dimension table with the dates within the model, create a new calculated table with the below function:

Calendar = calendarauto()

The next step is to create a relationship between this new date table and the Cases table on the Date columns.

Since the Cases table follows the same format, in this scenario we’ll see a 1 to 1 relationship with a bi-directional filter. This means that the Cases table will filter the Calendar table and vice versa.

You will also notice that the calendar icon next to our Date column in the fields pane is gone - but the Date column on the new Calendar table has it. This is referring to the date hierarchy within the model. The Date column on the Cases table no longer has the hierarchy because we’ve created a relationship between that column and the Calendar table - so the hierarchy (Year > Quarter > Month > Day) resides with the Date column on the Calendar table.

Percent % Change Over Time Measure

Now for the fun part - writing a measure that will dynamically calculate the percent change over a time period. I say dynamic here because we will use the date hierarchy in a visual and we want our users to be able to drill up and down in this hierarchy and we need the measure to take into account which level of the hierarchy we are in for its calculation. So what do we do? We’ll leverage variables in this next measure just so the dax is easier to understand and maintain.

Cases Change =
var prev_value =
if(hasonevalue('calendar'[date].[day]),
    calculate([cases],previousday('calendar'[date])),
    if(hasonevalue('calendar'[date].[month]),
        calculate([cases],previousmonth('calendar'[date])),
        calculate([cases],previousyear('calendar'[date]))
        )
    )
return
divide( ([cases] - prev_value), prev_value, 0)

The first section we’ll declare our variable prev_value and the goal here is to grab the sum of cases from the previous time period based on the level of the hierarchy that’s in our visual - day, month, or year. We’ll use the hasonevalue() function to check if the date column from our new calendar table has only one distinct value - and this function will return a boolean true or false based on this criteria. Since measures evaluate in the context of any filters applied both within the measure and by the visual, using the date column as a dimension in our visual provides that filter context.

In the column references for the hasonevalue() function, we’ll look for distinct values in each level of the date hierarchy. We’ll use a nested if statement to check for the three levels we want to use (day, month, year). Once an if statement evaluates to true, we’ll use the calculate() function to evaluate our Cases measure we created in Part 1, filtering for the corresponding previous time period using the functions previousday(), previousmonth(), and previousyear(). The result of this expression will be saved in our prev_value variable. This variable can be used inside of the expression following the return statement.

When it comes to writing the math for the percent change, we can really appreciate the use of variables in dax. The basic equation to calculate this value is to subtract the old value from the new value and then divide the result by the old value - so we’ll just swap in our measures and variables to make this happen. Since we will have time periods with no data or dates in a previous relative time period (data started reporting in March), we will have at least one instance of this equation trying to divide the current number of new cases by zero. This does not compute - so let’s wrap the equation in the divide() function. The third parameter in this function allows us to specify a value in case of division by zero. Let’s go with zero.

Let’s rinse and repeat this for all of our metrics so we can produce percent change over time for each of our metrics - deaths, hospitalizations, cases, and tests.

Once we graph these measures, we’ll notice that we have March in the data at 0% change (since we don’t have any February data) and the September value as of this writing only accounts for a few days - so we don’t have a full month to compare to August. Of course, we can just filter any date after March 31st out using the filter pane, and maybe add a footnote to the report to explain that the September (or any current month) would be comparing an incomplete set of data to make our users aware. We can also do this with measures so that the data model can make this determination for us regardless of the date range of data that gets loaded.

Filtering Date Dimension Using Measures

Complete Report Month =
if(
    endofmonth('cases'[date]) = endofmonth('calendar'[date]),
    1,
    0
)

We’ll need to be able to filter each visual based on the value this measure calculates and so we can’t rely on the boolean true/false values. The if statement will produce integers to represent the boolean values instead - 1 being true and 0 being false. To accomplish this, we’ll use the endofmonth() function to check what date is the last date in the month in the current filter context in both our calendar table and our cases table. This date will be missing from our data in the cases table for future dates, so any month after August 2020 will be flagged as false, or zero.

No Previous Month =
if(
    isblank(calculate([cases],previousmonth('calendar'[date]))),
        1,
        0
    )

To determine if we have data in the previous month to compare against, we’ll use the same approach to calculating the integer value of a boolean so that we can use this in a visual level filter in the filter pane. The calculate() function and the previousmonth() function will evaluate the existing cases measure for the previous month and the isblank() function will determine if the result of this expression has a value or not. If there is no data, isblank() will evaluate to true and we’ll slap a 1 on this record for true, and if the previous month does contain a value we’ll slap a zero on it for false.

With these two new measures, we can add these to the filter pane for each of our visuals and filter to only display months from the calendar table that have a complete month of reported data and exclude any months that do not have data in relative prior months. Cutting out the extra months on the graphs will clean up the look and give us a less distracted view of the percent change, month to month. Throw some conditional formatting on the columns to highlight the ups and downs and we’re good to go!

COVID-19 Trends - % Change Over Time Report