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

Thanks, Jos,

 

I tried this, copying your code into the Advanced Editor, and I ended up with this:

 

 

If I expand all the columns in the "Data" column, I get the following:

 

 

Not sure where I'm going wrong....

 

 

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

Oh...I didn't get it all. Beyond Data.Column6 I have: 

It's probably worth noting that the index column contains nothing but zeros, and the Custom column is all Errors. 

 

Another note: My original query included steps to promote the first column as headers, the "change type" commands that always follow promoting headers, and a command to remove column 5 (a blank column in my source). When I added your code after those lines, I got exactly the same result as I did when I just wrote your code in after the source lines. 

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    5 years ago

    "Another note: My original query included steps to promote the first column as headers, the "change type" commands that always follow promoting headers, and a command to remove column 5 (a blank column in my source). When I added your code after those lines, I got exactly the same result as I did when I just wrote your code in after the source lines."

    Yes, you should keep your original steps in. And when you added my code in, did you make sure that the (previous) step being referenced in my first line was the last of your initial steps (and not the first)?

    Regards