Date/Time Calculation Matrix Issue

[Anonymous]

Hi there,

I am trying to calculate the total shift duration worked by employees by using Datediff between Login & Logout. Since there could be more than just 1 login/logout in a day due to interruption, I am using the following measure:

Shift Duration = VAR MinimumShift = MIN(Data[Login])
VAR MaximumShift = MAX(Data[Logout])
VAR DifferenceSeconds = CALCULATE(DATEDIFF(MinimumShift, MaximumShift, SECOND))
VAR Hours = INT(DifferenceSeconds/3600)
VAR Minutes = INT(MOD(DifferenceSeconds-(Hours*3600),3600)/60)
VAR Seconds = ROUNDUP(MOD(MOD(DifferenceSeconds-(Hours*3600),3600),60),0)
RETURN
Hours*10000+Minutes*100+Seconds

The purpose for this measure is to calculate the difference between login & logout taking into consideration multiple rows for the same employee and same date, and also to return the result in HH;MM;SS after formatting it.

This is working just fine on row level of a table/matrix, but it is not calculating properly on column/row totals.

Can you please advise? Thank you 

[AllisonKennedy · Accepted solution]

@Anonymous  You need to provide the same context to the measure as you've provided with the table visual. 

https://dax.guide/functions/table-manipulation/ should help you do this.

Try something like: 

Shift Duration Final = 
SUMX ( SUMMARIZE ( Data, Data[EmployeeID], Data[DateKey] ) , [Shift Duration] ) 

[Anonymous]

Thank you @AllisonKennedy - It worked like a charm 🙂