CODEWITHSUNDEEP
powerbidax
Conner
Conner

Reputation: 307

Calculate the difference between groups in Power BI

This is my table 

Machine   Date        Hours    Interp
A         12/12/2019   250      AR
B         12/12/2019   186      NAR
C         12/13/2019   77       NAR
A         12/19/2019   277      MC
B         12/20/2019   195      NAR
A         12/30/2019   300      NAR

What I need to do is calculate the difference in hours between oil samples by Machine. So I want to add a column.

Machine   Date        Hours    Interp  Difference
A         12/12/2019   250      AR     0
B         12/12/2019   186      NAR    0
C         12/13/2019   77       NAR    0
A         12/19/2019   277      MC     22
B         12/20/2019   195      NAR    9
A         12/30/2019   300      NAR    23

I know I need to use EARLIER but I can't quite figure it out. Thanks in advance!

Upvotes: 1

Views: 397

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

Try something along these lines:

Difference =
VAR PrevDate =
    CALCULATE (
        LASTDATE ( Table[Date] ),
        ALLEXCEPT ( Table, Table[Machine] ),
        Table[Date] < EARLIER ( Table[Date] )
    )
VAR PrevHours =
    CALCULATE (
        MAX ( Table[Hours] ),
        ALLEXCEPT ( Table, Table[Machine] ),
        Table[Date] = PrevDate
    )
RETURN
    Table[Hours] - PrevHours

This uses ALLEXCEPT to remove all the row context except for the Machine name.

Upvotes: 1

Related Questions