Indexing based on value

[Anonymous]

Hi all,

With the corona virus threat going on I've been working with some of the data coming from Johns Hopkins University. The main page is here: https://github.com/CSSEGISandData/COVID-19

One of the charts I am trying to create is here below where they index all countries to start their day 1 from when their Confirmed cases is above 100.

If we focus particularly on the Confirmed cases CSV file (raw path accessible here: https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_ti...) does anyone know how to recreate this using Power BI?

If you're interested in the app I've built, I have made it public and am regularly updating it with posts on Linkedin. Will soon transfer and consolidate all this to GitHub.

• Power BI app: https://app.powerbi.com/view?r=eyJrIjoiZmQ0ZjU0M2YtZTljZC00NmE4LWE3MDEtOTZlNGQwYzAyZjlhIiwidCI6ImM4O...
• Articles on LinkedIn: https://www.linkedin.com/in/markmonfort/detail/recent-activity/posts/

I would really like to create the charts above as people I think would be very interested in these charts if we could make them interactive.

In the app I've built I am pulling the data in directly from the CSV listed on the JHU GitHub but happy if someone has a solution that works off directly downloading a CSV too.

Please help.

[Anonymous · Accepted solution]

@Anonymous 

Something went wrong with my complex measures, so I changed the dax to create 2 calculated columns, gladly they are less complex and straightforward. Hope this is what you are looking for.

Total #cases by country by dates = 
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Country/Region]=EARLIER('Table'[Country/Region])),'Table'[Period]=EARLIER('Table'[Period]))

Days since 100th case column = 
Var Date100casesbycountry = CALCULATE(MIN([Period]), FILTER('Table','Table'[Country/Region]=EARLIER('Table'[Country/Region])),FILTER('Table','Table'[Total #cases by country by dates]>=100))
Return DATEDIFF(Date100casesbycountry,[Period],DAY)

Pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EbTiAZpmovJGph_ov9Rf...

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @Anonymous  and @Greg_Deckler ... your posts were so timely and so helpful. I've been busy the last day and a half but the latter post fit the bill well enough. 

I have used this to look at other types of cases and look at other analysis too. 

cheers

Mark

[Greg_Deckler]

I didn't have time to look at the data yet, but perhaps a columm like this:

Days since 100th case column =
  VAR __Table = FILTER('Table',[Country] = EARLIER([Country])
  VAR __Date = 'Table'[Date]
  VAR __Table1 = 
      ADDCOLUMNS(
        __Table,
       "__Cases",
       SUMX(__Table,[Date] <= EARLIER([Date]),[Cases Per Day])
  VAR __100Day = MINX(FILTER(__Table1,[Cases] >= 100),[Date])
  VAR __Days = (__Date - __100Day) * 1.
RETURN
  IF(__Days < 0,BLANK(),__Days)

[Anonymous]

Thanks @Greg_Deckler  will try that

[Anonymous]

Hi Greg,

Built a small demo file that pulls from the raw CSV on gitbhub. You can get it off my Google Drive: https://drive.google.com/open?id=18OxFHjXiWbAiOHw0X0jUkYLS5C0iUocL 

tried your formula but could not get it to work. Can you please take a look? Or anyone else who might know, feel free to take a look?

Would be great to solve this.

Thanks everyone!!

[Greg_Deckler]

OK, @Anonymous I hadn't looked at the data and I'm lazy so I didn't test anything. Some syntax errors to be sure. Did this:

Days since 100th case column = 
  VAR __Table = FILTER('time_series_19-covid-Confirmed',[Country/Region] = EARLIER([Country/Region]))
  VAR __Date = 'time_series_19-covid-Confirmed'[Period]
  VAR __Table1 = 
      ADDCOLUMNS(
        __Table,
        "__Cases",
        SUMX(FILTER(__Table,[Period] <= EARLIER([Period])),[Value])
      )
  VAR __100Day = MINX(FILTER(__Table1,[__Cases] >= 100),[Period])
  VAR __Days = (__Date - __100Day) * 1.
RETURN
  IF(__Days < 1,BLANK(),__Days)

PBIX is attached below. You have lots of 1's due to province/state.