WTD, MTD, YTD working hours of each employee with different timestamps for each day using DAX

[manuvats]

This is a sample data. I need to calculate working hours in DAX.

Working hours for one day = Last Timestamp of the day - First Timestamp of the day

It is easy to calculate for one day but I can't seem to figure out how to calculate total working hours WTD, MTD, YTD.

Every day timestamps are different and we want working hours for each Worker(Name) separately. Please guide. I can share the actual data as well if anybody requires.


@amitchandak @Anonymous @Jihwan_Kim @daxer-almighty @PaulOlding @Fowmy 

[Anonymous · Accepted solution]

[Total Man-Hours] =
24 * SUMX(
	SUMMARIZE(
		T,
		T[Name],
		T[Date]
	),
	CALCULATE(
		MAX( T[Timestamp] ) - MIN( T[Timestamp] )
	)
)

[manuvats]

Hi Daxer
Thanks for the reply. Can you exlplain me why you have multiplied by 24 in the begining of the formula? Sorry for the late reply.

[Anonymous]

If you think carefully, you'll find out yourself. Hint: What does a difference between datetimes mean?

[manuvats]

Ok. But the straightaway subtraction does not give the difference. I used Datediff  without using '24' to find hours. Anyway, your solution worked and thanks for that.

[Anonymous]

@manuvats 

A diff between datetimes returns the number of days including decimal parts which stand for the percentage of a day. So, 1.25 days means 24 hours and 1/4 of a day which is 24 and 6 hours (24 * 1/4).

[manuvats]

You can specify the interval in the 3rd argument of DATEDIFF - second, minute, hour, day, week, month, quarter and year.

[Anonymous]

Mate, I'm talking about a direct difference between datetimes, not the function. Why would I want to use the function if it always truncates the units of time to whole ones? Even better. Using a function is always slower than a direct subtraction.