Covid-19 Cumulative cases since the date that hit 100 cases

DAX DAX Calculations
1

Hi all gurus,

I have been working on a Covid -19 data model based on information from European Disease Prevention Centre. The data is presented in daily new cases and deaths, not total cases. Please see attached my model and source files.

Country_Flags.csv (21.8 KB) Covid 19 Data.xlsx (234.9 KB) COVID-19 Dashboard.pbix (491.0 KB) Data_Extract_From_World_Development_Indicators.xlsx (290.5 KB)

image
image2160Ă—1198 463 KB

Recently I found a nice chart on an article and am triggered to replicate as it would be nice to show the speed the cases have grown since the first 100 cases.

image
image599Ă—896 144 KB

I’m hitting a road block where I don’t know how to reset the date on which the cases pass 100 as Day 0 and so forth.

Any ideas to solve this interesting problem is welcome.

Many thanks!

2

@kbi,

Check out the following video. Coincidentally, Ruth covered exactly how to do this in last week’s “DAX Fridays”:

  • Brian
2 Likes
3

Hi Brian,
It’s great! Now I grasp the idea of how to make it work.
I still have a problem though, the dataset used in the Youtube video is running total of confirmed case. While my dataset is NEW case daily. Do you know how to create a running total calculated column in Power BI (either DAX column or M)?
Thanks.

4

@kbi,

Sure – @sam.mckay has nearly a dozen videos on cumulative totals, but here are two that should give you everything you need to do this analysis:

  • Brian
5

@BrianJ

Yes I know these cumulative measures. However, it is measure. Whereas in this case I need a calculated column rather than a measure in the data table in order to show the 100th case chart.

I’ve searched a lot but apparently there’s no way to create a running total calculated column…

6

@kbi,

You can do this in a number of ways as a calculated column. Here’s one way to do it using the
EARLIER function:

If you still have questions after trying this, just give a shout and I’ll be happy to work through a specific solution with you.

  • Brian
7

Just FYI - for those of you interested in doing your own COVID-19 analyses in Power BI, Gil Raviv had an outstanding post on his site this week on this topic, focused on how to prep the data in Power Query:

  • Brian
8

Hi Brian,
Thanks heaps for your guidance. Let me try our the Earlier function and if I cannot solve it I will shout! :slight_smile:

9

Hi @kbi, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!