Forum Discussion

ripstaur's avatar
ripstaur
Icon for Helper III rankHelper III
5 years ago
Solved

Calculating daily differences

I have a data set that contains columns for counties and states in the US, and COVID-19 case counts and deaths in each county each day. The numbers are cumulative...if there was one case on day one, ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi ripstaur ,

    First create an index column;

    Then create a column as below:

    Daily Cases =
VAR _maxvalue =
    CALCULATE (
        MAX ( 'Table'[Cases] ),
        FILTER (
            'Table',
            'Table'[County] = EARLIER ( 'Table'[County] )
                && 'Table'[Index] < EARLIER ( 'Table'[Index] )
        )
    )
RETURN
    'Table'[Cases] - _maxvalue

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

ripstaur's avatar
ripstaur
Icon for Helper III rankHelper III
5 years ago

Thank you , Edhans. This problem is like calculating moving ranges, but I need to be able to do it per county across the date range. I put together a sample (with an extra column showing the desired result of the calculation. When shifting from one county to another, the date is going to drop back to the starting date for the time period. In the result cell for that record, the value should equal the "cases" value for that day.

StateCountyDateCases Desired Result (Daily Cases)
AlabamaAbbot1/1/20201 1
AlabamaAbbot1/2/20201 0
AlabamaAbbot1/3/20202 1
AlabamaAbbot1/4/20202 0
AlabamaAbbot1/5/20203 1
AlabamaAbbot1/6/20203 0
AlabamaAbbot1/7/20203 0
AlabamaAbbot1/8/20204 1
AlabamaAbbot1/9/20204 0
AlabamaAbbot1/10/20205 1
AlabamaBillings1/1/20200 0
AlabamaBillings1/2/20200 0
AlabamaBillings1/3/20202 2
AlabamaBillings1/4/20202 0
AlabamaBillings1/5/20203 1
AlabamaBillings1/6/20204 1
AlabamaBillings1/7/20204 0
AlabamaBillings1/8/20204 0
AlabamaBillings1/9/20205 1
AlabamaBillings1/10/20205 0
DelawareDeForbes1/1/20200 0
DelawareDeForbes1/2/20201 1
DelawareDeForbes1/3/20201 0
DelawareDeForbes1/4/20201 0
DelawareDeForbes1/5/20203 2
DelawareDeForbes1/6/20203 0
DelawareDeForbes1/7/20205 2
DelawareDeForbes1/8/20207 2
DelawareDeForbes1/9/20207 0
DelawareDeForbes1/10/20207 0
v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi ripstaur ,

First create an index column;

Then create a column as below:

Daily Cases =
VAR _maxvalue =
    CALCULATE (
        MAX ( 'Table'[Cases] ),
        FILTER (
            'Table',
            'Table'[County] = EARLIER ( 'Table'[County] )
                && 'Table'[Index] < EARLIER ( 'Table'[Index] )
        )
    )
RETURN
    'Table'[Cases] - _maxvalue

And you will see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

  • ripstaur's avatar
    ripstaur
    Icon for Helper III rankHelper III
    5 years ago

    Thanks, Kelly! This worked like a charm. I'm still trying to make a PowerQuery solution work, but this DAX solution is absolutely one great solution. My question now becomes - which is a more efficient solution--the DAX modeling or a PowerQuery one, if we can figure it out? I am going to replicate this for the number of deaths per county as well. Since there are about 3400 counties in the US, and the daily report also include extras (e.g., US territory counts, counts of cases that were reported without a county affiliation), I add 3400+ new records every day , with a State column, a county column, a lat and long column, a FIPS column, cumulative cases, cumulative deaths, daily cases and daily deaths. So would it be more efficient to do this in modeling, or querying? 

     

    Best regards,

     

    Rip