Total Time difference sum not working

[TaroGulati]

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

[bhanu_gautam · Accepted solution]

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

[v-stephen-msft]

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

[Bibiano_Geraldo · Accepted solution]

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]

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.

[bhanu_gautam · Accepted solution]

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