Forum Discussion

ripstaur's avatar
ripstaur
Helper 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!

samdthompson's avatar
samdthompson
Memorable Member
5 years ago

Hello, this would be btter tackled with DAX but you can do it in power query. 

 

1. Sort the data by say country, region and date.

2. add index starting at 0

3. duplicate table and alter the index to starting at 1

4. merge tables together with a full outer join using country and index as joins

5. you will now have the cumulative column and the cumulative column offset by one day in the same row. Subtract one from the other and you have your daily movement. Obviously for the first and last value for each country/region you will need to add some logic into the subtraction

 

 

ripstaur's avatar
ripstaur
Helper III
5 years ago

Thanks, samdthompson! This is a good hint...if I can make it work, I will post the code as a solution.