In my last tutorial here, you learned how to apply basic time intelligence in Power BI to build your own COVID-19 dashboard following the initial tutorial, in which we imported confirmed and death cases from Johns Hopkins dataset using Power Query. In this tutorial, we will continue our journey and learn how to apply a simple What-If scenario to calculate the estimated recovered and active cases based on the estimated days to recover.
Before you start this tutorial follow Part 1 and Part 2, or download this PBIX file if you prefer to take a shortcut and skip the previous parts.
A Short Background
What we have done so far? In Part 1 we imported the confirmed and death cases from Johns Hopkins’ dataset and merged between the two CSV files. Then, in Part 2, we tackle the challenges of working with cumulative data. We created the calendar table:
Calendar = CALENDAR (
MIN ( 'Covid-19 Cases'[Date] ),
MAX ( 'Covid-19 Cases'[Date] )
)We created the measures to correctly sum up the cumulative confirmed and death cases no matter which visuals you use.
Total Confirmed =
VAR sum_confirmed =
SUMX (
VALUES( 'Covid-19 Cases'[Country/Region] ),
SUMX (
VALUES ('Covid-19 Cases'[Province/State] ),
CALCULATE ( MAX ( 'Covid-19 Cases'[Confirmed] ) )
)
)
RETURN
IF (sum_confirmed = 0, BLANK(), sum_confirmed )Total Deaths =
VAR sum_deaths =
SUMX (
VALUES( 'Covid-19 Cases'[Country/Region] ),
SUMX (
VALUES ('Covid-19 Cases'[Province/State] ),
CALCULATE ( MAX ( 'Covid-19 Cases'[Deaths] ) )
)
)
RETURN
IF (sum_deaths = 0, BLANK(), sum_deaths )To create the daily new cases, we used DATEADD against our Calendar table.
Daily New Cases =
VAR current_day = [Total Confirmed]
VAR prev_day =
CALCULATE (
[Total Confirmed],
DATEADD ( 'Calendar'[Date], -1, DAY )
)
RETURN
IF ( ISBLANK ( prev_day ), BLANK(), current_day - prev_day )In the next part of this tutorial, you will learn how to create a 5-day running average to smooth the jitters of the curves.
Calculating Estimated Active Cases
Tracking recovery cases globally may be logistically challenging. At one point, due to data reliability issues, Johns Hopkins removed the recovered cases from their dataset. Instead of tracking the actual recovered cases, we can calculate an estimation for the recovered cases. If you know that it takes X days to recover, you can calculate how many confirmed cases were reported X days ago and subtract the number of deaths to estimate how many were recovered every day.
And this is what we started in Part 2 of the tutorial. We used a static number for our X which is the estimated days for recovery:
Estimated Days to Recover = 17Next, we calculated the estimated recovered cases by calculating the previous period using DATEADD that offsets the time period by subtracting the current date with Recovery in Days.
Estimated Recovered =
VAR prev_period =
DATEADD (
'Calendar'[Date],
- [Estimated Days to Recover],
DAY
)
RETURN
CALCULATE (
[Total Confirmed] + [Total Deaths],
prev_period
) - [Total Deaths]Once we had the estimated recovered cases, we found the estimated active cases by subtracting Total Confirmed with Estimated Recovered.
Estimated Active Cases = [Total Confirmed] - [Estimated Recovered]From here, we could create line charts that show the estimated active cases over time. See how China shows the bell-shaped curve, while the other countries still showed exponential growth of estimated active cases in April.
What-If
In this tutorial, we will change the Recovery in Days static measure, from 17 to a dynamic measure that is fed by the user’s selection in a slicer. This will allow you to change the estimated days to recover and see how different numbers affect the estimated active cases. This is a classic example to the What-If scenarios you can implement in Power BI.
To turn the static Recovery in Days from 17 days into a dynamic parameter that you can set in a slicer, we will now create a What-If Parameter.
In Modeling tab, select New parameter in the What if section.
In the What-if paramater dialog box, enter Days in the Name box. Keep Whole number as the Data type. Enter 0 as Minimum and 40 as Maximum. Keep 1 as Increment, and keep the checkbox on for Add slicer to this page. Then, click OK.
You now have a new slicer on the report page and move it from 0 to 40. Note that it doesn’t have any effect yet.
Let’s examine what happened under the hood. You can select the new slicer and see in the Fields pane, that several new artifacts were created under the new Days table. The slicer shows the Days column of the Days table.
Clicking on the table or Days column will reveal a GENERATESERIES function in the formula bar:
Days = GENERATESERIES(0, 40, 1)
This formula returns a new calculated single-column table with the numbers 0, 1, 2, … , 40. You can change the minimum, maximum, and increment values of the slicer by changing the corresponding arguments 0, 40, and 1 in the formula bar.
Now, click the Days Value measure that was generated under the Days table. You can see that its measure is as follows:
Days Value = SELECTEDVALUE('Days'[Days])SELECTEDVALUE will return the value you select in the slicer. If you create a Card visual and set its field to Days Value, you will see that the Card will show you the selected Days in the slicer. Move the slicer and see how the number in the Card visual changes accordingly.
You can delete the Card visual. It’s time to “wire” the slicer to our report. In the Fields pane, select the measure Estimated Days to Recover and modify the formula from 17 to the [Days Value] as shown below.
Estimated Days to Recover = [Days Value]You can now change the Days slicer and see how each change affects the curve in the Estimated Active Cases by Date line chart or the Estimated Recovered card.
To do some magic with Power BI and What-If, you can create a page that will show all the possible selections you can make side-by-side.
In the line chart above, you can see how we can flatten the curve if we could somehow reduce the time to recover. Each line in the chart represents a different value for the estimated days to recover.
To create the left Line Chart, place the Line Chart visual on the canvas and drag and drop Date to Axis, Estimated Active Cases to Values and Days to Legend.
I was blown away when I saw this technique for the first time. I hope your mind is now racing – Imagine what you can do with the What-If technique in your own reports.
To illustrate how this technique is relevant to our current analysis let’s say that in Italy we have only 10,000 available hospital beds and that 20% of all active cases need to be hospitalized (These numbers are fictitious and serve for illustration purposes only). Now, let’s say we need to evaluate different treatments and how they will affect our hospital capacity. Each treatment reduces or increases the recovery time according to the line chart below (5, 10, 15, 20, 25, 30 days). We can then take advantage of our What-If technique above to see that we can select the treatments that will have 10 or fewer days to recovery. Otherwise, we will reach our red line of 150% capacity in our hospitals.
In my next tutorial, I will share more techniques to analyze the curve as I implemented the published COVID-19 dashboard. Including this one:
Hope you find this tutorial useful. Stay tuned to learn how I built this report. You can install it as a Power BI app from Microsoft AppSource here.
The post COVID-19 dashboard in Power BI: What-If appeared first on DataChant.