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.

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Again amitchandak , sorry for the late reply, in this link you can find a copy of the model.  

     

    The main issue is that in the weekly context it's adding all accumulated cases on each day of that week instead of the cases per day on each day calculated previously with a measure. 

     

    Many thanks for looking into it!!

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

      Anonymous , I created a new column 

       

      new cases = [Confirmed] - sumx(FILTER(COVID,[Country/Region] =EARLIER(COVID[Country/Region]) && [Province/State] = EARLIER(COVID[Province/State]) && [Observation Date] =EARLIER(COVID[Observation Date])-1),[Confirmed])

       

       

      This works better. The only problem is that at a few place data is not correct. so data is showing negative 

       

      Also this measure 

      CasesPD = 

VAR Varcasesperday = [Total Cases Confirmed] - CALCULATE([Total Cases Confirmed], DATEADD('Calendar'[Date],-1,day))

RETURN
IF([Total Cases Confirmed]=BLANK(),BLANK(),Varcasesperday)
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi amitchandak I was looking at the column you created and it was working quite well on both context, the daily and the weekly one, exactly what I wanted to calculate. The problem is that for some reason, there are some days where the daily cases are overstated and it does not match with the accumulated figures (Current day accumulat = Cases acutal day + last day total accumualted cases), do you know why is the reason of that difference? Example here

         

        The previously created measure casesPD is calculating correctly the daily context but when passed to the weekly one, it does not work. 

         

        Much appreciated the help you have given me with this.