Power BI and Forecasting

Since my last post on the coronavirus data before the Labor Day weekend (two posts ago?), I’ve watched a few videos on using Power BI to track the coronavirus. Most of them show just the usual: total number of cases, deaths and recoveries by global, country or states shown as a card, a table, line graph or bar graph. And, of course, the usual maps. Plus, there are a few instances of pie or donut graphs and other varieties. I didn’t really see anything unique but I only saw a few videos, maybe 3 or 4 videos.

But there was one YouTuber who used the forecasting feature in Power BI, back in March, and I was curious about that forecast feature. Now, at this point, I wouldn’t use the forecast feature because the history does not determine the future – it’s our behavior that will determine the future course of the virus.

Power BI’s Forecast Tool

Here’s the forecasting example, using data as of 9/6/2020. The bright blue line is the actual case counts and the gray line and shaded area represent the forecast. The shaded area represents the 95% chance of where the future case counts will go. The interesting thing about the forecast tool in Power BI is that it can take into account the “seasonality” of the data, which in this case I estimate to be 7 days as a weekly “seasonal” run with the weekend slowing down on determining the result all of the coronavirus laboratory tests. The forecast tool has the “seasonality” set as Auto – I couldn’t change it. You can see the weekly peaks and troughs in the graph and the forecast kind of mimics the prior movements. (Yes, I’m still playing around with the imagery.)

Source of Data: Wikipedia
https://en.wikipedia.org/wiki/Template:2019–20_coronavirus_pandemic_data/United_States_medical_cases

To find the forecast tool, go to the analytics icon found in the Visualization box, next to the paint roller (the format tool) which is found next to the Field icon.

I learned that there are a couple of “rules” in order for the forecast tool to work. Here’s a web page I found for the instructions:

https://powerbi.microsoft.com/en-us/blog/introducing-new-forecasting-capabilities-in-power-view-for-office-365/#:~:text=In%20Power%20View%2C%20you%20can,from%20a%20series%20of%20data.

Here are the main points I learned when trying to use the forecast tool:

The tool works only on line graphs; bar graphs or combos do not work at this time (Sept 2020).
It will work on only one line.
It does not appear to work on measures – my moving average measure would not offer the forecast tool – it was grayed out.
There is supposedly a limit of 1000 data points before the forecast tool flames out.

The other thing I learned as a general rule, not specific to the forecast tool, is if something does not work with a particular graph, try another type of graph (besides Googling for instructions). You might learn something new in trying out things.

But, like I wrote earlier, normally I wouldn’t use the forecast tool for predicting the course of the coronavirus because it is the behaviors that will determine the path. I have been reading about the mobility data that some companies can cull from the smartphones or data on restaurant reservations as being a signal of people moving about. I thought I would try searching for such data to see if anything pops up and lo and behold, OpenTable, Apple and Google appeared.

OpenTable Restaurant Reservation Data

Actually, earlier in the year I had tried OpenTable but I didn’t find anything that suggested an API connection or a download of data, but yesterday I finally was able to grab some data. Previously, I think you had to subscribe to their data but I think OpenTable decided to open up this data partially in the name of public service. Anyway, the data is in the form of year over year percentage increase or decrease from the same week of the prior year (so comparing week 11 of 2020 to week 11 of 2019). Here’s a couple of graphs depicting how restaurant seating plunged around mid-March and did not start recovering until about mid-May. The baseline for comparison is at zero and most of the bar graphs are hanging below that zero line. Yep, that straight horizon line is the zero baseline. You can see that just a handful of countries/states have managed to recover restaurant seatings in September (towards the right side of the bar graph).

(I just realized that the Bookmarks that I was experimenting with did not change the title from Country to State or City. I don’t have time to go back and fix.)

By Countries

By US States

By US Cities

Those tables with red numbers? Those are percentage change from last year that decreased. The numbers listed are averages of all states, cities, or whatever, for the month and the grand total is the grand average. So, restaurant reservations decreased from last year.

Now how would I use this to predict the coronavirus cases? It’s kind of hard to say. At this point, it is more interesting from an economic standpoint rather than from a predictive standpoint.

Here’s a graph where I focused on US states for the time period August 15 – September 6 and changed the graph type a little to see if I could discern anything. September 5th is odd because all states (and most countries) plunged into the negative on that day, so I don’t know what is going on that day, but on September 6, you can see restaurant seatings for some states skyrocket above zero. Actually, it looks like in September restaurant reservations were starting to happen.

Here’s another set of graphs showing OpenTable data for Memorial Day weekend, July 4th weekend, and Labor Day weekend (what I could pull) and you can see that the Labor Day weekend definitely had improvements in restaurant seatings – quite a few states had reservation percent change from last year of over zero. Memorial Day weekend didn’t have any states with seatings above zero percentage and the 4th of July was not much better, even when I looked at Arizona, Georgia, Florida or Texas. However, we had surges throughout the South over the summer, especially June/July, so, those surges may be related to something else – probably parties? bars? Restaurants may have still been mostly closed during the summer.

Mobility Data from Smartphones or iPads

Now, I have the Google and Apple data but I’m going to have to think about how I can use these for predictive purposes, if at all.

Here’s some graphs culled from the Google data where I see some interesting historical stories but how do I use for prediction? These graphs represent percentage change from a baseline of 5 weeks: January 3 to February 6.