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 is not correct. I am using the below measure:

 

Time Difference = Var Result_Second =  SUMX('Table',DATEDIFF(MIN('Table'[Start Date]),MIN('Table'[End Date]),SECOND))
Var Result_hour = INT(Result_second/3600)
Var Result_minutes = INT(MOD(Result_second,3600)/60)
Var Result_second_final = MOD(Result_second,60)
return FORMAT(Result_hour, "00") & ":" & FORMAT(Result_minutes, "00") & ":" & FORMAT(Result_second_final, "00")
 
I think it happening due to sumx and it seems to be a common issue. Do you know how to resolve it with measures. I am trying to avoid the calulated column. 
 
Thanks
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:

     

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    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")

  • 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.

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    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:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi TaroGulati ,

     

    Pls has your problem been solved? If so, accept the helpful replies as solutions. This will make it easier for the future people to find the answer quickly.
    If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.

    Best Regards,
    Stephen Tao