Forum Discussion
Calculate the difference between two dates in different rows
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 |
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 dayVAR Hours = QUOTIENT(MOD(DiffMinutes, 1440), 60) -- Remaining minutes converted to hours
RETURNIF(ISBLANK(PrevDatetime),BLANK(),FORMAT(Days, "0") & " Days " & FORMAT(Hours, "0") & " Hours")
2 Replies
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/339586.
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.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 dayVAR Hours = QUOTIENT(MOD(DiffMinutes, 1440), 60) -- Remaining minutes converted to hours
RETURNIF(ISBLANK(PrevDatetime),BLANK(),FORMAT(Days, "0") & " Days " & FORMAT(Hours, "0") & " Hours")
