Subtracting row values in DAX ; Power BI

Question

If I have data of this sort:

I'd like to calculate the time difference between a value in Bi and Bi+1, in PowerBI. That is, in a new a column, in the Query Editor (so writing in M), I want to have:

C3 = B3-B2

C4 = B4 - B3

and so on.

Do you know how to achieve this in PowerBI?

Solution

You can use a calculated column like the below to compare each row against the previous then compute the difference in seconds using the DATEDIFF function:

Difference in Seconds =
DATEDIFF (
    'Table'[Time],
    CALCULATE (
        MIN ( [Time] ),
        FILTER ( ALL ( 'Table' ), [ID] < EARLIER ( 'Table'[ID] ) )
    ),
    SECOND
)

If you want to compare it against the previous ID time value, regardless if the time value is higher you can simply use:

Difference in Seconds =
DATEDIFF (
    'Table'[Time],
    CALCULATE (
        MIN ( [Time] ),
        FILTER ( ALL ( 'Table' ), [ID] = EARLIER ( 'Table'[ID] ) - 1 )
    ),
    SECOND
)

Using LOOKUPVALUE:

Difference in Seconds =
DATEDIFF (
    'Table'[Time],
    LOOKUPVALUE ( 'Table'[Time], 'Table'[ID], [ID] - 1 ),
    SECOND
)

UPDATE: Adding the column from source via M Language.

Use something like this in a custom column:

=Table.AddColumn(#"YourLastStep", "Diff",
 each
    (try DateTime.From(#"YourLastStep"[Time]{[ID]-2})
    otherwise DateTime.From([Time])) - [Time]  
 )

Hope it helps.

Comments

[Mariah]

thank you for the comment; I now realize I want this new column to be created in the Query Editor, so this calculation can be automated on refresh. Do you know how to perform this in M?

[alejandro zuleta]

@Mariah, In M it would be like the example I posted in my answer. Give it a try.