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!

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

Power Query only alternative:

let
  Source = YourSource, 
  #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), 
  #"Added Custom" = Table.AddColumn(
    #"Added Index", 
    "Custom", 
    each 
      let
        Previous = try #"Added Index"[Cases]{[Index] - 1} otherwise 0, 
        Result   = if Previous > [Cases] then 0 else [Cases] - Previous
      in
        Result
  )
in
  #"Added Custom"

which adds a 0-based Index column and then a Custom column which gives your required results. Amend the line Source = YourSource accordingly to fit in with your previous steps.

Regards

  • 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