How to calculate numerical difference between rows

[Anonymous]

I have a column of accumulated values. I would like to create a measure that gets the daily difference between values so I get the actual daily value.

Thank you

[CNENFRNL · Accepted solution]

Hi, @Anonymous , you might want to use a calculated column in the scanrio.

In case there are some gaps in the date column, the formula would be a bit verbose; of coz, this formula applies as well if there's no gaps between dates.

Daily Cases = 
VAR __prev_date =
    MAXX (
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] < EARLIER ( 'Table'[Date] ) ),
        'Table'[Date]
    )
RETURN
    'Table'[Accumulative Cases]
        - MAXX (
            FILTER ( 'Table', 'Table'[Date] = __prev_date ),
            'Table'[Accumulative Cases]
        )

A solution of measure is also available,

# Daily = 
VAR __current_date = MAX ( 'Table'[Date] )
VAR __previous_date =
    MAXX (
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] < __current_date ),
        'Table'[Date]
    )
RETURN
    MIN ( 'Table'[Accumulative Cases] )
        - CALCULATE (
            MIN ( 'Table'[Accumulative Cases] ),
            'Table'[Date] = __previous_date
        )

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous · Accepted solution]

Hi @Anonymous 

You can try my way to achieve your goal by power query editor.

I build a sample table like yours.

Firstly duplicate this table names Table 2.

Add index columns in per table.( Table1 From 0 , Table 2 From 1)

Then merge two tables by index columns. (left outer table1)

Expand case column in Table 2 then replace null = 0 .

Build a custom column:

[Cases]-[#"Table (2).Cases"]

This is my advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+9DoMgGIXhWyHMDPxrb8DNqU06EAZaSexik68yePdVxKRYmJ83JznG4P49DW4hXKLOPyA4WBCnnGKCKbbE4FvwnxioSnD3w7Qma6FrE2OAfaPZigA/BYtFB6/obcWvbg6wFYhf/hKxJyH+QAz1Dp7jgTJiOol4CY+HIkedv5O5stM1VeT0S+fIxelUU/b0qM1VKGztFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Cases = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Cases", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Table (2)", {"Index"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Cases"}, {"Table (2).Cases"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table (2)",{{"Index", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,0,Replacer.ReplaceValue,{"Table (2).Cases"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each [Cases]-[#"Table (2).Cases"])
in
    #"Added Custom"

Result is as below:

You can download the pbix file from this link: How to calculate numerical difference between rows

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[CNENFRNL · Accepted solution]

Hi, @Anonymous , you might want to use a calculated column in the scanrio.

In case there are some gaps in the date column, the formula would be a bit verbose; of coz, this formula applies as well if there's no gaps between dates.

Daily Cases = 
VAR __prev_date =
    MAXX (
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] < EARLIER ( 'Table'[Date] ) ),
        'Table'[Date]
    )
RETURN
    'Table'[Accumulative Cases]
        - MAXX (
            FILTER ( 'Table', 'Table'[Date] = __prev_date ),
            'Table'[Accumulative Cases]
        )

A solution of measure is also available,

# Daily = 
VAR __current_date = MAX ( 'Table'[Date] )
VAR __previous_date =
    MAXX (
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] < __current_date ),
        'Table'[Date]
    )
RETURN
    MIN ( 'Table'[Accumulative Cases] )
        - CALCULATE (
            MIN ( 'Table'[Accumulative Cases] ),
            'Table'[Date] = __previous_date
        )

[TomMartens]

Hey @Anonymous ,

you can create a calculated column to create the change between two days like so:

change = 
var currentDate = 'tablename'[Date]
return
calculate(
sum('tablename'[cases])
,'tablename'[date] = currentdate - 1
)

Hopefully, this is what you are looking for.

Regards,

Tom