Calculate the difference between two dates in different rows

[leorrn]

Hi,

I'd like to calculate the difference between dates, splited in different rows, returing the difference in the format "Days Hours".

Do you guys have any idea?

The table is already ordered by "Planta"and Datetime (ASC order). If there is no previous record, it can return null.

Thanks in advance community 🙂 

Table example

Planta	Datetime	Date	Index	Index.1	datediff
Aglaonema	15/1/25 9:01	15/01/2025	10	11
Aglaonema	18/1/25 16:31	18/01/2025	9	10
Aglaonema	22/1/25 8:50	22/01/2025	8	9
Aglaonema	27/1/25 10:24	27/01/2025	7	8
Aglaonema	1/2/25 15:14	01/02/2025	6	7
Aglaonema	3/2/25 13:05	03/02/2025	5	6
Aglaonema	5/2/25 10:22	05/02/2025	4	5
Alecrim	19/9/24 8:04	19/09/2024	49	50
Alecrim	21/9/24 9:53	21/09/2024	48	49
Alecrim	23/9/24 9:39	23/09/2024	47	48
Alecrim	25/9/24 10:30	25/09/2024	46	47
Alecrim	26/9/24 7:59	26/09/2024	45	46
Alecrim (2025-01)	21/1/25 17:03	21/01/2025	53	54
Alecrim (2025-01)	22/1/25 10:31	22/01/2025	52	53
Alecrim (2025-01)	23/1/25 10:57	23/01/2025	51	52
Alecrim (2025-01)	3/2/25 13:13	03/02/2025	50	51
Aloe Aristata	19/9/24 8:04	19/09/2024	65	66
Aloe Aristata	23/9/24 9:39	23/09/2024	64	65
Aloe Aristata	26/9/24 8:10	26/09/2024	63	64
Aloe Aristata	29/9/24 10:09	29/09/2024	62	63
Aloe Aristata	1/10/24 15:15	01/10/2024	61	62

[rajendraongole1 · Accepted solution]

Hi @leorrn  - Please find the attached pbix you can use DAX to calculate the time difference between consecutive rows within each "Planta" group

Datediff_Days_Hours =

VAR PrevDatetime =

    CALCULATE(

        MAX('timdiff'[Datetime]),

        FILTER(

            'timdiff',

            'timdiff'[Planta] = EARLIER('timdiff'[Planta]) &&

            'timdiff'[Datetime] < EARLIER('timdiff'[Datetime])

        )

    )

VAR DiffMinutes = DATEDIFF(PrevDatetime, 'timdiff'[Datetime], MINUTE)

VAR Days = QUOTIENT(DiffMinutes, 1440)  -- 1440 minutes in a day

VAR Hours = QUOTIENT(MOD(DiffMinutes, 1440), 60)  -- Remaining minutes converted to hours

RETURN

    IF(

        ISBLANK(PrevDatetime),

        BLANK(),

        FORMAT(Days, "0") & " Days " & FORMAT(Hours, "0") & " Hours"

    )

[Greg_Deckler]

@leorrn See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
 ( __Current - __Previous ) * 1.