Skip to main content
cancel
Showing results for 
Search instead 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

Reply
learner03
Post Partisan
Post Partisan

Calculate Number of hours between first and last time stamp

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

akapoor03_0-1626657569752.png

 

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?
1 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]
    )
)

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Solution Specialist
Solution Specialist

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

 

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

akapoor03_0-1627025287249.png

 

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?

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]
    )
)

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors