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!

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