0

I'm working with a Covid-19 dataset (this one) but it seems harder work because the daily totals are cumulative totals rather than 'new cases' each day.

I've been trying to get an accurate figure for daily new cases based on the running total for each country but I can't get it right - is anyone able to offer any advice on how best to do this please?

Nick Roberts
  • 15
  • 5

1 Answers1

0

Cumulative Frequency of the cases is the total number of cases as of the current date. Mathematically, if the number of cases on first three days are x, y and z, then the cumulative frequency will be:

CF = X + y + z

If you simply want the count for the first day then the formula will be:

x = CF - (y +  z)

Now, we have to implement this in Power BI which is a bit tricky considering the dataset. There is a column for each date in the dataset so it's harder to get previous cumulative frequency. You will have to unpivot the columns into rows so that the dates will come on rows for each country instead of the columns. Here is the code snippet for the unpivoting:

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Province/State", "Country/Region", "Lat", "Long"}, "Attribute", "Value")

After that, you will simply sort the dataset by date and get the previous value.

Hamza Mazhar
  • 1
  • 2