Forum Discussion

TaroGulati's avatar
TaroGulati
Helper III
1 year ago
Solved

Total Time difference sum not working

Hi all,    I am having difficulty in total sum for the below scenario:   I am simply doing the time difference between start and end date. At the row level it is working perfectly but total...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    TaroGulati , Try creating a measure using

     

    DAX
    Time Difference =
    VAR TotalSeconds = DATEDIFF(MIN('Table'[Start Date]), MAX('Table'[End Date]), SECOND)
    VAR Result_hour = INT(TotalSeconds / 3600)
    VAR Result_minutes = INT(MOD(TotalSeconds, 3600) / 60)
    VAR Result_second_final = MOD(TotalSeconds, 60)
    RETURN FORMAT(Result_hour, "00") & ":" & FORMAT(Result_minutes, "00") & ":" & FORMAT(Result_second_final, "00")

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi TaroGulati ,

    Update your DAX measure to look like this:

    Time Difference = 
VAR TotalSeconds = 
    SUMX(
        'Table', 
        DATEDIFF('Table'[Start Date], 'Table'[End Date], SECOND)
    )
VAR TotalHours = INT(TotalSeconds / 3600)
VAR TotalMinutes = INT(MOD(TotalSeconds, 3600) / 60)
VAR TotalSecondsFinal = MOD(TotalSeconds, 60)
RETURN 
    FORMAT(TotalHours, "00") & ":" & FORMAT(TotalMinutes, "00") & ":" & FORMAT(TotalSecondsFinal, "00")

     

    Your output will look like this:

     

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

Hi TaroGulati  - you can use a measure that correctly calculates the total time difference for all rows without reformatting until the final step

 

create measure as below:

 

Time Difference =
VAR TotalSeconds =
SUMX(
'Table',
DATEDIFF('Table'[Start Date], 'Table'[End Date], SECOND)
)
VAR Hours = INT(TotalSeconds / 3600)
VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
VAR Seconds = MOD(TotalSeconds, 60)

RETURN
FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")

 

It avoids the row-level formatting issue and ensures that the total at the bottom of the table is calculated correctly.