Tracking Coronavirus using Power BI

Visual representations make trends and differences easy to spot.

Trying to make sense of the news during the Coronavirus crisis provides a powerful example of how difficult it can be to extract meaning from raw data. Like everybody else I have been struggling to interpret the daily deluge of numbers so I can determine whether we are at least moving in the right direction.

Without wishing to trivialise anything, one small benefit for me has been to clarify the whole question of exponential growth. If you are just interested in an overview of how cases are growing, or falling, across a representative sample of countries including the UK, the graph above shows you how things developed in April. Further down you can see a live graph showing where we are at now.

The information conveyed by this graph can be understood at a glance. It compares the daily rate of growth of reported Coronavirus infections between China, Spain, Italy, the UK and the US. Seeing a line on a downward curve with a clear trend would seem to indicate successful social distance measures while a more erratic trajectory points towards less effective measures.

The daily rate of growth is calculated by taking the number of infections reported today and dividing this number by the number reported yesterday. You will get an answer in the range 1.0 to 1.3 which represents 0% to 30%. From this number you can calculate the number of days required for the infected number to double, for example, at 1.2 the numbers double every four days, at 1.06 every 12 days and at 1.02, doubling takes 35 days. Knowing this you can plot your exponential curve and estimate such critical projections as the number of people requiring intensive care within the next thirty days (as a proportion of the number infected).

I have been plotting these numbers on a spreadsheet since March 1st. It appears to be the case that a daily rate of increase number above 1.2 is the point at which a lock down is triggered and below 1.02 is the point at which restrictions can begin to be eased.This gives you a working range to monitor progress in any country you may be interested in. There are a number of different sources for this data available online. The one I have been using is Worldometers.

Though not the point of this blog, you may be interested in how to get from a spreadsheet to an online live display of your data. This is done using Power BI as part of my Microsoft 365 subscription. The data is stored in an Excel spreadsheet in MS One Drive and Power BI is used to visualise and publish the data.

This visualisation is a lot simpler than many you will find online and, therefore, may be useful to quickly see key trends. One thing that was puzzling me was how close the numbers appeared to be (in the above graph) between Italy and Spain on the one hand and the US and UK on the other. The difference between these two groups of countries is that the former had a very rigorous lock down imposed while the latter went with something much looser, and yet the result seems much the same.I did a bit of rethinking on the graph for May, see below, and now the difference seems clear.

In this graph (May rate of growth) I have taken a slightly different approach by showing the daily rate of growth as a percentage value rather than today as a multiple of yesterday. To calculate this value you subtract yesterday’s number of infected (x) from today’s number (y) and divide the answer (z) by yesterday’s number (x), then multiply the answer by 100 to get a percentage. In Excel terms you can do this with the formula ((y-x)/x)*100. This is always a useful formula in business to calculate how much better or worse we are doing this month or year compared to last month or year. With the rate of increase expressed as a percentage it is then very easy to calculate doubling time. To do this you can use the rule of 70 (or 72), you just divide 72 by the percentage rate of growth. The answer is a pretty close approximation of the number of days until the number of infections will double. I don’t know why this works but it is a very useful rule to know about if you ever need, e.g., to calculate the effects of compound interest on your mortgage or pension. Getting back to our Power BI data visualisation and my puzzlement about the the similar effects of different lock down policies, as appears to be the case on the top graph. In fact, with our slightly adjusted calculation we can get a very clear picture of the different outcomes and see quite a significant gap between the top two lines, representing the US and UK, and the lower two showing the trajectory of Spain and Italy. On this view, the daily rate of increase in infections is shown as up to four or five times higher with a weak lock down.

I hope you find the above useful and if I or any of the team at Macnamara can offer any help or support during this crisis please do feel free to drop me a line.

Like this article?

Share on twitter
Share on Twitter
Share on linkedin
Share on LinkedIn
Share on email
Share by Email

Subscribe to our monthly newsletter

Get the best IT tips and Office ideas in your inbox

Further reading

how to be calm and resilient during a work crisis

How To Stay Calm and Be Resilient During a Business Crisis

You can’t escape the C-word at the moment. 

And by C-word we’re not referring to “coronavirus” – nor “COVID-19” (though, you can’t escape those either).

The word of the day is crisis. Because you know what – COVID-19 is just one crisis. It’s surely not the first, and it won’t be the last.

So here’s one particularly valuable thing you can learn from this situation: how to stay calm and logical in the face of any crisis – big or small – that impacts your company.

Read More »

Want to hear what customers have to say?

Watch out customer story videos and find out today...

Cookie Notice

This website uses cookies to ensure you get the best experience on our website. Learn More.

Scroll to Top

Subscribe to our monthly newsletter.
Get the best IT tips and Office ideas in your inbox.

We promise to keep your information safe. Unsubscribe at any time. Read our privacy policy.

Get the Ebook

"Five High-Value Business Initiatives That’ll Provide Massive Impact and Help You Get Noticed at Work"

Get this empowering Ebook in your inbox — when you subscribe to the Macnamara Newsletter. What’s in the Newsletter? Insightful articles, invites to exclusive events, powerful ideas, free training resources. Don’t miss out – subscribe today.

We promise to keep your information safe. Unsubscribe at any time. Read our privacy policy.

Get the Ebook

"Five Ideas That'll Help you Tame Unruly Systems and Team Members"

Get this empowering Ebook in your inbox — when you subscribe to the Macnamara Newsletter. What’s in the Newsletter? Insightful articles, invites to exclusive events, powerful ideas, free training resources. Don’t miss out – subscribe today.

We promise to keep your information safe. Unsubscribe at any time. Read our privacy policy.

Get the Ebook

"Five Powerful Ideas That’ll Take Your Personal and Business Development to the Next Level"

Are you an Office Manager who’s looking for next-level ideas? You need to read this Ebook — you’ll get it for free when you subscribe to the Macnamara Newsletter. What’s in the Newsletter? helpful articles, invites to events, powerful ideas, free training resources. Don’t miss out – subscribe today.

We promise to keep your information safe. Unsubscribe at any time. Read our privacy policy.

Get the Ebook

"Your Ultimate Guide To Office Delegation"

Get this empowering Ebook in your inbox — when you subscribe to the Macnamara Newsletter. What’s in the Newsletter? Insightful articles, invites to exclusive events, powerful ideas, free training resources. Don’t miss out – subscribe today.

We promise to keep your information safe. Unsubscribe at any time. Read our privacy policy.