Set up a day 0

[Danielktn]

Hi, I am doing an analisys for the Corona Virus. My table has the following columns:

-Date

-Cases

-Country

and a dates table

I would like to graph the cummulated cases by country and dates. I get it with a cummulated messure. 

The problem is when I try to do a graph setting up for each country the day when it had the first 100 cummulated cases in order to analise in parallell the evolution for several countries. I think that I should find the date 0 for each country and after add a column with the difference between Date an Day0per Country but I am not able to get it.

Any idea?

thanks

[Greg_Deckler · Accepted solution]

@Danielktn - Found the file, now will see if I can find the thread. But the calculated column was something like 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)

[Greg_Deckler]

I helped someone else out with almost the exact same issue on this forum. Let me see if I can locate the thread.

[Greg_Deckler]

Can't find it, but you may find the PBIX files attached to these useful:
https://community.powerbi.com/t5/Data-Stories-Gallery/Coronavirus-Outbreak/td-p/914037
https://community.powerbi.com/t5/Data-Stories-Gallery/Coronavirus-Visualisation/td-p/936959
https://community.powerbi.com/t5/Data-Stories-Gallery/Distirbution-of-Coronavirus/td-p/956702

[Greg_Deckler · Accepted solution]

@Danielktn - Found the file, now will see if I can find the thread. But the calculated column was something like 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)

[Greg_Deckler · Accepted solution]

And the thread:
https://community.powerbi.com/t5/Desktop/Indexing-based-on-value/m-p/976847

[Danielktn]

Thanks a lot Greg. It works fine and it is exactly the same case.

[Greg_Deckler]

Glad I tracked it down!!