Forum Discussion

leorrn's avatar
leorrn
Regular Visitor
1 year ago
Solved

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 orde...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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"
        )
rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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"
    )