Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating weekly COVID reported cases.

Hello Kind people,   I am trying to calculate the weekly repoted cases of Covid by country, the problem is my data set does not show new cases each day but the total cases accumulated to date.    ...
  • v-jingzhang's avatar
    v-jingzhang
    6 years ago

    Hi Anonymous 

     

    Try these measures, and the data you marked also calculated correctly.

     

    TotalCasesConfirmed = CALCULATE(SUM(COVID[Confirmed]),LASTDATE('Calendar'[Date]))

CasesPerDay =
VAR Varcasesperday = [TotalCasesConfirmed] - CALCULATE([TotalCasesConfirmed],DATEADD('Calendar'[Date],-1,DAY))
RETURN
IF([TotalCasesConfirmed]=BLANK(),BLANK(),Varcasesperday)

CasesPerWeek =
VAR Varcasesperweek = [TotalCasesConfirmed] - CALCULATE([TotalCasesConfirmed], DATEADD('Calendar'[Date],-7,DAY))
RETURN
IF([TotalCasesConfirmed]=BLANK(),BLANK(),Varcasesperweek)

     

    And the result:

     

     

    Best Regards,

    Community Support Team _ Jing Zhang

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

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous 

 

Try these measures, and the data you marked also calculated correctly.

 

TotalCasesConfirmed = CALCULATE(SUM(COVID[Confirmed]),LASTDATE('Calendar'[Date]))

CasesPerDay =
VAR Varcasesperday = [TotalCasesConfirmed] - CALCULATE([TotalCasesConfirmed],DATEADD('Calendar'[Date],-1,DAY))
RETURN
IF([TotalCasesConfirmed]=BLANK(),BLANK(),Varcasesperday)

CasesPerWeek =
VAR Varcasesperweek = [TotalCasesConfirmed] - CALCULATE([TotalCasesConfirmed], DATEADD('Calendar'[Date],-7,DAY))
RETURN
IF([TotalCasesConfirmed]=BLANK(),BLANK(),Varcasesperweek)

 

And the result:

 

 

Best Regards,

Community Support Team _ Jing Zhang

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Great v-jingzhang ,you have made my day!! I figured out the issue I was having with my orginal measures was because I did not have that last bit of "LASTDATE('Calendar'[Date])" on the total Cases Confirmed measure

TotalCasesConfirmed = CALCULATE(SUM(COVID[Confirmed]),LASTDATE('Calendar'[Date]))

 

The measures you provided was the one I was looking for, it works perfectly fine and the weekly total of cases matches with the weekly accumulated cases. 

 

Many thanks both of you v-jingzhang amitchandak to help me with this.

 

Have a good day!