I am writing an off-topic article today but I promise I will circle back later in the week with a follow up article bringing it back on topic. In this article, I am going to share a COVID report I built today, and later in the week I will post a follow up article where I show how I created the full report so you can see me work through an end to end problem.
Background – COVID in Australia
Firstly, some background. Thankfully, Australia has been spared the worst of the COVID deaths that have occurred in many countries. This is one of the benefits of being an island nation on the other side of the world. This outcome has been achieved by locking the national borders to international travellers, forcing all returning residents into mandatory 14 day hotel quarantine, and jumping on any outbreaks with lockdowns of various severities.
As my Australian readers would well know (and maybe some readers from around the world, too), Australia is now entering a new stage of its fight against COVID-19. The Delta strain has appeared on our shores and by all reports (and observations), this virus is much more contagious than previous strains. The response from two of the state governments in Australia (NSW and VIC) is to hard lock down 50% of the country in an attempt to eradicate the Delta strain from Australia. At the time of this article, Australia is experiencing a 7 day average of 114 cases per day (in a population of 25 million people). Despite the effort, the signs are that control is slipping away despite the lockdowns.
Without wanting to get too political in this article, I must say that I have been frustrated by the lack of availability of all relevant data provided by the Australian media and Governments. There is a lot of commentary about how highly contagious the Delta strain is (vs previous strains), but not much commentary about how severe the Delta cases are, nor what impact vaccinations are having on outcomes. When I look at data coming from overseas (mainly the UK), it would seem that the Delta variant in a partially vaccinated population is significantly less deadly than previous strains of COVID, 10 to 20 times less deadly, in fact.
Let’s Look at All the Data
So with that as a background, I decided to do my own research. This is one of the benefits of being a Power BI professional – if you don’t get to see the analysis that you need, then you can simply build your own. I set out to find all the relevant data, build a report, and then form my own conclusion if this Delta variant outbreak in partially vaccinated Australia is less deadly than previous outbreaks, or not.
Spoiler alert 19th July 2021 – it’s too early to say based on the Australian data, but there should be a clear answer by the end of July 2021.
After I loaded and completed a brief exploration of the data, I decided to group and then split the data into 4 phases as follows.
I acknowledge that this is somewhat arbitrary, but it is not without some logic. I analysed the case and death data to find some logical groupings of time where it made sense (to me at least) to split and group the data. I have called these “phases” and not “waves”, as the third phase could not really be called a “wave”; it was a period of relative calm in the country. I explain the logic for my splits in the video below. If you think there is a better way to split the data, then please say so in the comments below.
Case Fatality Rate
After completing my analysis, here is the case fatality rate for each of my phases (as at 17th July 2021).
The way to read this chart is that phase 2 was the most fatal in Australia, with 3.94% CFR (deaths as a percentage of recorded cases). All other phases are compared against that worst period to determine how many times “less fatal” was each other phase vs phase 2. Phase 3 is showing that it was 33 times less fatal than phase 3, and phase 4 (so far, at least) is running at 17 times less fatal.
I specifically want to call out “so far” for this phase for a very important reason. The data clearly shows that, historically, deaths lag the initial spike in case numbers by about 45 days. It would appear from the data that if there is going to be a spike in deaths in Australia from the cases recorded in this latest phase, then we will start to see the spike appear in the next 2 weeks. Hopefully this will not happen, and the theory that the Delta strain and the benefits of vaccination have significantly reduced the IFR will be shown.
I will update this article of the next few weeks to keep you informed what the numbers show.
Video Run Through
I recorded a brief video explaining the report I have built, and also showing how I came to the decision on where to start and end the phases.
Edit: When I first released this article, I incorrectly referred to the Infection Fatality Rate when what I was actually showing was the Case Fatality Rate (thanks Paul for pointing this out to me). My calculations divide deaths by recorded cases. The number of recorded cases is always less than the infection rate, because not all infections are recorded. I have corrected the reference were possible, but please note that in the video I continue to refer to Infection Fatality Rate whereas the correct term in Case Fatality Rate.
And here is an interactive copy of the report if you would like to take a closer look. I have now scheduled this to auto refresh every day, so it should stay up to date from here on in.
Watch Out for my Next Article
If you are interested in seeing how I used Power BI to build this report, keep an eye out for my more technical article on this topic later in the week.