Calculate Number of hours between first and last time stamp

[learner03]

I am calculating Number of scan/hour for the warehouse forklift drivers. It shows output for current day, previous day, week to day, MTD and FY YTD.

For calculatin number of Hours, till now I was using 7.5 as shift hours, but, I need to calculate hours based on first scan done by driver and the last scan by driver(timestamp). How can I calculate this hour difference.

The time stamp column looks like this (It doesn't have any start or end, it is just one coloumn)-

For example, for current day I was using:-

// I took 6 as start time of shift-

Current Day =

VAR CurrentDate = MAX('Calendar'[Date])

RETURN

DIVIDE(

CALCULATE(SUM('ULDs - History'[lines]), 'Calendar'[Date] = CurrentDate),

If((((HOUR(NOW())-6)*60+MINUTE(NOW()))/60)>7.5,7.5,((HOUR(NOW())-6)/60) 

)

Previus day-

Previous Day =

VAR CurrentDate= MAX('Calendar'[Date])

VAR Prev_Day =

CALCULATE(MAX(Calendar[Date]), FILTER('Calendar', Calendar[Date] < CurrentDate && 'Calendar'[If work day]=1))

VAR Trasnactions_Prev_Day =

CALCULATE(

SUM('ULDs - History'[lines]),

'Calendar'[Date] = Prev_Day

)

RETURN

DIVIDE(

Trasnactions_Prev_Day,

7.5,

0

)

Week to date-

VAR CurrentDate = LASTDATE('Calendar'[Date])

VAR DayNumberofWeek = WEEKDAY(LASTDATE('Calendar'[Date]),2)

VAR Transactions =

CALCULATE(

SUM('ULDs - History'[Inner]),

DATESBETWEEN('Calendar'[Date], DATEADD(CurrentDate, -1*DayNumberofWeek, DAY), CurrentDate))

VAR holidays = CALCULATE(COUNTROWS('Calendar'), 'Calendar'[If work day] = 1,

DATESBETWEEN('Calendar'[Date], DATEADD(CurrentDate, -1*DayNumberofWeek, DAY), CurrentDate))

VAR TotalHours = (DayNumberofWeek-1) * 7.5 +

(((HOUR(NOW()) - 6)*60 + MINUTE(NOW()))/60) 

RETURN

DIVIDE(

Transactions,

TotalHours,

0

)

MTD =

VAR Transactions =

CALCULATE(

SUM('ULDs - History'[Inner]),

DATESMTD('Calendar'[Date])

)

VAR holidays = CALCULATE(COUNTROWS('Calendar'), 'Calendar'[Month(Current_or_Previous)] = "Current Month", 'Calendar'[If work day] = 0)

VAR CurrentDate = MAX('Calendar'[Date])

VAR TotalHours =

(DATEDIFF(DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1), CurrentDate, DAY) - holidays) * 7.5 +/*(Diff of dates - holidays) * 7.5*/

(((HOUR(NOW()) - 6)*60 + MINUTE(NOW()))/60)

RETURN

DIVIDE(

Transactions,

TotalHours,

0

)

FY YTD =

VAR Transactions =

CALCULATE(

SUM('ULDs - History'[Inner]),

DATESBETWEEN ( 'Calendar'[Date], DATE(YEAR(MAX('Calendar'[Date])), 7, 1), TODAY () )

)

VAR CurrentYear = MAX('Calendar'[Fin Year])

VAR holidays = CALCULATE(COUNTROWS('Calendar'), 'Calendar'[Fin Year] = CurrentYear, 'Calendar'[If work day] = 0)

VAR CurrentDate = MAX('Calendar'[Date])

VAR TotalHours =

(DATEDIFF(DATE(YEAR(CurrentDate), 7, 1), CurrentDate, DAY) - holidays) * 7.5 + /*(Diff of dates - holidays) * 7.5*/

(((HOUR(NOW()) - 6)*60 + MINUTE(NOW()))/60) /*hours of today untill now */

RETURN

DIVIDE(

Transactions,

TotalHours,

0

)

How can I edit all these formulas to calculate total hours based on first time stamp and last time stamp of that day?

@Fowmy @parry2k @amitchandak @v-diye-msft @Jihwan_Kim @Ashish_Mathur 

[Anonymous]

I reckon, it would make things easier if you change the data model by transforming the data in PowerQuery.

Use Group By to summarize the data by Driver and Date 

add 2 calculaitons:

FirstScan = Min (timestamp)

LastScan = Max(timestamp)

Then you can add another column to get hours between them.

In the data model, join this table with Date and Driver tables. Done!

[Mohammad_Refaei]

You can simply create supoprting columns in your calendar table such as:

FirstStamp = MOD ( MIN ( RELATED ( 'ULDs - History'[Timestamp] ) ), 1 )
LastStamp = MOD ( MAX ( RELATED ( 'ULDs - History'[Timestamp] ) ), 1 )

WorkingHours = LastStamp - FirstStamp

Format them all as time and use them

[learner03]

@Mohammad_Refaei 

Thanks for your reponse. But meanwhile no one was answering so till that time I created a cross join table and got this view below-

I created min and max coloumn and difference of max min coloumn to get number of hours.

Now I need to get total number of hours each employee worked  week to date, month to date and FY YTD based on above table.

How can I do that?

or is there any easier approach?

[Mohammad_Refaei · Accepted solution]

Well, the solution I provided is not considering employee level granularity since I didn't know much about your model.

Please share the table structrure for the 'ULDs - History' as well and explain the employee identifier (I assume it is the [Packer ID].

A syntax like this should work for you:

YTD Hours =
CALCULATE (
    SUM ( CrossJoinedTableName[Difference] ),
    ALLEXCEPT ( CrossJoinedTableName, CrossJoinedTableName[Packer ID] ),
    DATESYTD ( 'Calendar'[Date] )
)

MTD Hours =
CALCULATE (
    SUM ( CrossJoinedTableName[Difference] ),
    ALLEXCEPT ( CrossJoinedTableName, CrossJoinedTableName[Packer ID] ),
    DATESMTD ( 'Calendar'[Date] )
)

You will need to add a column with the weeknumber:

WeekNumber = WEEKNUM(CrossJoinedTableName[Date],2)

WTD Hours =
CALCULATE (
    SUM ( CrossJoinedTableName[Difference] ),
    ALLEXCEPT (
        CrossJoinedTableName,
        CrossJoinedTableName[Packer ID],
        CrossJoinedTableName[Fin Year],
        CrossJoinedTableName[WeekNumber]
    )
)