Excel examples for your work, sports and more.: Coronovirus in Netherlands Power BI report

#66 Coronavirus in the Netherlands: Power BI report

Update 27/3/2020, report v4:
I added a measue for R0 - reproduction number of COVID19 virus.

Update 21/3/2020, report v3:
I update this blog-post today because I made a new version of the Coronavirus-report. The changes in thin blog-post I marked below with:
'Update 21/3/2020 begin ... end'
See also my new post: https://worktimesheet2014.blogspot.com/2020/03/coronavirus-in-netherlands-embedded.html
for the report embedded in my blog, and which is daily refreshed (at aprox. 14:00).

Update 15/3/2020, report v2:
I update this blog-post today because I made a new version of the Coronavirus-report. The changes in thin blog-post I marked below with:
'Update 15/3/2020 begin ... end'

Spain has declared the state of emergency the other day because of the Coronavirus. My kids were already at home this week because all schools were closed. And this week also the office of the company I work for, Visma Labs Spain (supporting Visma|Raet in the Netherlands) was closed, and since then I'm working at home. And the Coronavirus crises also lead to a new project I had to work for, to create a report with Coronavirus info in the Netherlands.
In the Netherlands, the official Coronavirus numbers per Dutch municipality are published daily (at aprox. 14:00) by RIVM on:

https://www.rivm.nl/coronavirus-kaart-van-nederland

Here you can download a CSV file with this data. I was wondering if this information was disclosed in a more user-friendly way in Spain, maybe with a REST-API, but it wasn't. My Google-search lead me to this blog-post of Marcos Merino:

https://www.genbeta.com/actualidad/italia-crea-web-datos-abiertos-sus-casos-coronavirus-estas-iniciativas-particulares-buscan-espana

from which I learned that in Italy the governemnt offered Coranvirus-data as open-data here:

http://opendatadpc.maps.arcgis.com/apps/opsdashboard/index.html#/b0c68bce2cce478eaac82fe38d4138b1
(see in bottom-right corner 'Download CSV')

and also on GitHub: https://github.com/pcm-dpc/COVID-19

And Merino also tells that in Spain, the Twitter-user Ecléctikus also shared the Coronavirus-data
of the Spanish government:

https://www.mscbs.gob.es/profesionales/saludPublica/ccayes/alertasActual/nCov-China/situacionActual.htm

on GitHub, see: https://nosolodatos.netlify.com/es/covid19/coronavirus

So I wondered if in the Netherlands someone did the same with the Dutch Coronavirus-data from RIVM, and that was the case. Jonathan de Bruin of Utrecht University offers the RIVM-data here:

https://github.com/J535D165/CoronaWatchNL

And for the meta-data of the datasets on this page. see:

https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/0PD4JM

I downloaded ('pull- request') his Coronavirus-repository, which also has improments of the RIVM dataset (e.g. it has a Province field, and stores all historic values). And after that, I processed this dataset with Microsoft Power BI . See fig.1-2 for the result, and for the PBIX-file, see Downloads below.

Update 15/3/2020 begin:
I just made a small improvement to the report: I created a hierarchy for the fields Province and Municipality, so that in Chart 1 you can do a drill down in the map from Province to Municipality, see fig.3 for an example, where I drilled down for province Brabant (the one which is most affected).
(Map-)Chart 1, on municpality-level, makes it visible that the 2 most affected cities within Brabant, Tilburg and Breda, are close, something that (bar-)Chart 2 does not do.

BTW: I created the report yesterday (14/3/2020), and in the screenshots in this blog-post you can also see this date as the 'filter-date' in Chart 1. After a new 'pull' of the GitHub-dataset (15/3/2020 22:00), and a 'Refresh Data' in Power BI, I had the the new data in my report, so without any effort thanks to de Bruins good work.

When you upload a Power BI report to the cloud Power BI Service, you automatically can get 'Quick Insights', see fig.4 for some examples.

Update 15/3/2020 end

Update 21/3/2020 begin
De Bruin added new datasets on his GitHub page, with information about:
- # deaths ("aantal doden")
- # hospitalized ("aantal in ziekenhuis")

I added this new metrics in a new page (3) to the report, together with the old one (# cases ("aantal Corona gevallen"), in a table and 2 line-charts, for both cumulative numbers as 'deltas' (new number per day), see fig.5.

For the 'delta', I used this DAX-formula, which I got from here:

https://community.powerbi.com/t5/Desktop/Delta-Values-from-Previous-Day-Using-Multiple-Columns/td-p/186473

Delta Corona gevallen = SUM(rivm_corona_in_nl_daily[Aantal Corona gevallen]) - CALCULATE(SUM(rivm_corona_in_nl_daily[Aantal Corona gevallen]);DATEADD(rivm_corona_in_nl_daily[Datum];-1;DAY))

NB: De Bruin's source for this data were the RIVM press releases with daily update (below the map) on:
https://www.rivm.nl/nieuws/actuele-informatie-over-coronavirus

Some other improvements of the v3-report are:
- Corona-cases for which RIVM had no data for Municipality, are now having value
"Onbekend" for the fields Province and Municipality in the report
- Chart 3 uses now for stacked bar chart the Province-hierarchy (so enabling drill-down from Province to Municipaliy)
- Page 2 has now also a Date-filter

Update 21/3/2020 end

Update 27/3/2020 begin
The DAX for the measure R0 - reproduction number of COVID19 virus is:

R0 = [Delta Corona gevallen] / CALCULATE([Delta Corona gevallen];DATEADD(rivm_corona_in_nl_daily[Datum];-1;DAY))

For the new R0-chart, see fig.6

For more info about R0, see e.g:

https://labblog.uofmhealth.org/rounds/how-scientists-quantify-intensity-of-an-outbreak-like-covid-19

Update 27/3/2020 end

fig.1: Power BI report Dutch Coronavirus-data - page 1 (see also fig.3)

fig.2: Power BI report Dutch Coronavirus-data - page 1

fig.3: Power BI report Dutch Coronavirus-data - page 1: Drill-down for Province = Brabant, showing detail for city Breda

Update 15/3/2020 begin

fig.4: Power BI Quick Insights

Update 15/3/2020 end

Update 15/3/2020 begin

fig.5: Power BI table and charts for metrics #Corona-cases, #Deaths, # Hospitalizations

Update 15/3/2020 end

Update 277/3/2020 begin

fig.6: Power BI chart with R0

Update 27/3/2020 end

To conclude, I'd like to share something that was done yesterday here in Madrid, where Coronavirus is hitting hard. Via a Whatsapp-group of school, they asked every child to make a drawing with a rainbow with the text "We'll get trough this", and to hang it out on the balcony, and at 22:00 go out on the balcony and give a big aplaus for all the people who work in public survice (doctors, nurses, police etc.) to thank them for their hard work in dangerous circumstances. And also to support all people in Madrid who now have to stay at home (you can only leave home for the most necessary things as going to the supermarket). For more info about this initiative, see:

https://www.hoy.es/sociedad/salud/dibujos-ventanas-llevadera-20200314172924-ntrc.html?ref=https%3A%2F%2Fwww.google.com%2F

Below the drawing my 2 sons made.