Weekly readings – 20th June 2020

What I wrote

I wrote about the new partnership between Walmart and Shopify

Arguably the hottest topic in tech this week is the saga between Apple and Hey

I also talked a bit about Verisign, a company that makes most of the Internet work properly

If you are interested in Quick-Service-Restaurant franchise, I wrote about operating margin that can be expected by a franchisee

A couple of quick tutorials on SQL and rolling average in Power BI


If You Want Hertz, Have Some Hertz

How Robinhood Convinced Millennials to Trade Their Way Through a Pandemic. Robinhood now has 10+ million users and has become a phenomenon lately

The Observer Effect’s interview with Marc Andreessen

Stemming from the interview above, I found Marc’s previous post on productivity hack

A great post on Structured Procrastination

Structured procrastination means shaping the structure of the tasks one has to do in a way that exploits this fact. The list of tasks one has in mind will be ordered by importance. Tasks that seem most urgent and important are on top. But there are also worthwhile tasks to perform lower down on the list. Doing these tasks becomes a way of not doing the things higher up on the list. With this sort of appropriate task structure, the procrastinator becomes a useful citizen. Indeed, the procrastinator can even acquire, as I have, a reputation for getting a lot done.

Source: Structured Procrastination

The Risk of Outsourced Thinking

Google and HTTP

The Case for ARM-Based Macs

Amazon asks court to block former AWS marketing VP from working on Google Cloud Next speeches

How Large Is the Apple App Store Ecosystem?

Other stuff

The Death of Engagement. A good read on America’s foreign policy with China over the last administrations

A collection of free books from Springer

In Japan and France, Riding Transit Looks Surprisingly Safe

Architects have designed a Martian city for the desert outside Dubai

How to create Rolling average in Power BI without data tailing off in the end

Today, I will talk about how to do rolling average in Power BI and how to address the tailing off in the end.

Rolling average is a technique used to address the short term volatility and fluctuation of data. In this example, I used the Apple Mobility Data for the state of New York. The data is from 1st March 2020 to 14th June 2020, which is the latest date when the data is available as of this writing. My dataset has only two columns: date and mobility. Here is what it looks like in Power BI

Apple Mobility for 11th and 12th May 2020 is not available, but you don’t see on the graph above because I smoothed it out by setting the date field to “continuous”.

To create a rolling average in Power BI, it’s actually quite simple. Click on “New Quick Measure” and choose “Rolling Average”, then you just need to fill in the details

Depending on what you are trying to do, you can set an appropriate period as well as the “periods before” and “periods after” fields. In this example, I am looking at 30-day rolling average. Therefore, I set it up that way.

Your data will look like this. There are a few things worth pointing out:

  • To make a new quick measure work, the time variable on the X-axis has to be a date hierarchy
  • That’s why you no longer see the whole graph on the screen. Instead, to see every data point, you now have to scroll horizontally
  • Even though the data stops on 14th June 2020, the graph doesn’t stop until 14th July 2020. It is because right now, you don’t tell Power BI when to stop projecting the data. The value on 14th July 2020 is exactly the value of 14th June 2020 and the upward trend is misleading because the more the graph moves to the right, the fewer data points there are.

If you look at the code for the New Quick Measure, here is what it looks like

To fix the tailing off issue, you just need to modify the code a little bit in the “Return” part, as follows:

The result will look like this

You can see the original data with its own short-term fluctuation in orange and the 30-day rolling average in the blue line. After the code is modified, the blue line now stops on 14th June 2020.

To enhance user experience by eliminating the need to roll horizontally, make sure that the date hierarchy also has “Year” as you can see below

Don’t you think the line chart looks smoother and better now?

Hope this little tutorial helps