COVID-19 Data, getting increase in cases from previous day

[Anonymous]

Hi, I'm having a play with the COVID-19 dataset from Johns Hopkins, and trying to figure out how I can see the number of new cases from the previous day so I can plot something like the graph below from the Guardian.

I followed this tutorial to get my dataset, so I have a big table with State, Country, Date, Value columns.

Each State/Country combination has a row for each date eg: Victoria/Australia has a row for every date with that days cumulative total, as does New South Wales/Australia.  

However, not every row has a State eg: New Zealand only has country, State is Blank.

Essentially what I want to do is to add a new column, Increase, and for each row calculate the difference between "Value" from that row and the row which matches the State & Country columns, but the previous days date.  

NOTE: THis is not actual code!! Just a representation of what I'm looking for...

ie: this.Increase = this.Value - FindRow(State = this.State && Country = this.Country & Date - this.Date - 1).Value

I have no idea where to start!! Please help!!

FYI: I'm not a real data analyst, just a geek with Power BI looking to kill some time this weekend while I stay home!

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Please try the DAX like below:

Daily cases = 
VAR __CountyName = 'COVID'[County Name]
VAR __State = 'COVID'[State]
VAR __Yesterday =  DATEADD(COVID[Date],-1,DAY)
VAR __TodaysCases = 'COVID'[Cases]

RETURN  __TodaysCases - CALCULATE(
    SUM('COVID'[Cases]) , 
    FILTER(
        COVID, 
        COVID[Date] = __Yesterday &&
        COVID[County Name] = __CountyName &&
        COVID[State] = __State
    )
) + 0

Covid-19 sample 

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

[MFelix]

Hi @Anonymous ,

In this case the best option is not to adda new column but a calculated measure. Since measures are calculated based on context if you add the country or the state, or the full dataset the calculation will adjust accordingly.

Try something similar to:

Increase =
SUM ( Table[Column] )
    - CALCULATE (
        SUM ( Table[Column] );
        FILTER ( ALL ( Table[Date] ); Table[Date] = MAX ( Table[Date] ) - 1 )
    )

Replace the Tables and column names by the ones in your report.