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. 

 

I have taken the following 2 steps:

 

1) Created an aggreation measure of the total cases confirmed

Total Cases Confirmed = SUM(COVID[Confirmed])
 
2) Created a second measure to calculate the cases per day: 
CasesPD =
VAR Varcasesperday = [Total Cases Confirmed] - CALCULATE([Total Cases Confirmed], PREVIOUSDAY('Calendar'[Date]))
RETURN
IF([Total Cases Confirmed]=BLANK(),BLANK(),Varcasesperday)
 
The aboce measure works perfectly when working with cases per day, the problem arises when I change the context from a daily granularity to a weekly one and then it sums all the accumulated cases for each day of the week instead of all the cases per day in that single week.
 
Example data working with a daily granularity (Data here is correct)
DateCasesPD Total Cases Confirmed
16/08/2020               212,487         21,672,186
17/08/2020               209,672         21,881,858
18/08/2020                256,068         22,137,926
19/08/2020                273,374         22,411,300
20/08/2020                267,183         22,678,483
21/08/2020               270,751         22,949,234
22/08/2020               254,298         23,203,532

 

Below I want the table to show the sum all cases in the above table (representing week 34) , should be equalt to 1,743,733 cases during week 34 buit instead is showing the sum of all accumulated cases and another number. 

 

Example data working with a weekly granularity (This is not showing the right data)
WeekNumCasesPD Total Cases Confirmed
34   135,474,820.00                156,934,519.00

 

Could you please help me to create the correct measure so I can calculate the right amount for the weekly reported cases?. 

 

I am using the dataset found here, the one called "covid_19_data.csv". 

 

Many thanks in advance for your help.

 

Regards.

 

Gabriel.

 
  • 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.

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Join with a date table. Have the correct week defined there and analyze by week

     

    Any Weekday Week - Week of your choice
    https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

    Week wise analysis

    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello amitchandak thanks for your answer, I do have a calendar table in my model and a week number, but this is not the problem. 

       

      I have a relationship created between my calendar table and date table as well, the issue here seems to be the logic on the formula, it works well when I calculate the daily cases per day but does not show correctly when I change the table from daily to weekly as posted originally. 

       

      Could you shed some light on how can I calculate the measure so my weekly cases will be the sum of all cases per day on that specific week?

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        Anonymous ,  Can you share a sample pbix after removing sensitive data. So that I can check the issue. 

  • 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!