cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Date/Time Calculation Matrix Issue

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.

1 ACCEPTED SOLUTION
Super User

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

Try something like:

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

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

2 REPLIES 2
Super User

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

Try something like:

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

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helper I

Thank you @AllisonKennedy - It worked like a charm 🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.