Forum Discussion
Calculating Working Hours Between Two Dates/Times
Hi bvbull200 ,
You can try to use below calculated column formula to calculate valid working hour:
If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Work Hour =
VAR filtered =
FILTER (
ADDCOLUMNS (
CROSSJOIN (
CALENDAR ( [ACTIVITY_DATE], [LASTMODIFIEDDATE] ),
SELECTCOLUMNS ( GENERATESERIES ( 9, 18 ), "Hour", [Value] )
),
"Day of week", WEEKDAY ( [Date], 2 )
),
[Day of week] < 6
&& [TicketID] = EARLIER ( Table1[TicketID] )
)
VAR hourcount =
COUNTROWS (
FILTER (
filtered,
(
[Date] >= DATEVALUE ( [ACTIVITY_DATE] )
&& [Hour]
> HOUR ( [ACTIVITY_DATE] ) + 1
)
&& (
[Date] <= DATEVALUE ( [LASTMODIFIEDDATE] )
&& [Hour]
> HOUR ( [LASTMODIFIEDDATE] ) - 1
)
)
)
VAR remained =
DATEDIFF (
TIMEVALUE ( [ACTIVITY_DATE] ),
TIME ( HOUR ( [ACTIVITY_DATE] ) + 1, 0, 0 ),
MINUTE
)
+ DATEDIFF (
TIME ( HOUR ( [LASTMODIFIEDDATE] ) - 1, 0, 0 ),
TIMEVALUE ( [LASTMODIFIEDDATE] ),
MINUTE
)
RETURN
IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )
- bvbull2007 years agoHelper III
It is doing some calculation, but it clearly isn't the correct calculation.
I am currently displaying the information on a table and looking at the Req Created Date, Req Created At, First Acted Date, and First Acted At fields for comparison.
You can see the table below (with highlights added by me):
https://1drv.ms/u/s!Akyp0hrzA6TBqi099uvUd5rLhCvY
If we look at the first highlighted row (ID # 4199979), the Req Created Date is July 3rd and the Req Created At is 3:32:29 p.m. The First Acted Date is July 3rd and the First Acted At is 3:44:16 p.m. The Time To First Act should be counting the time between these two instances, which is 11 minutes and 47 seconds. The expected value would be
0.18 (as in 0.18 business hours).
For the row highlighted in purple, the expected value would also be 0.18.
These are basic examples. There are more complex ones to figure out as well, but I want to get the easy ones done first. An example of a more complex calculation would be row 6 (ID # 4199739) as that begins after 5 p.m. and goes through a weekend. The expected value on that would be the equivalent of (in business time) 18 hours, 2 minutes, and 37 seconds (18:02:37) or 18.03. This is figured because the start time for counting would be 8:00 a.m. the following day, then two full business days (9 hours each), then 2 minutes in to the next business day (Monday).
Here is the link to source data:
https://1drv.ms/x/s!Akyp0hrzA6TBqiukGz7Dvp4Kgx-8?e=EFV4dZ
Here is the link to the PowerBI project:
https://1drv.ms/u/s!Akyp0hrzA6TBqizIvVvfnZUOs5_9?e=RNirPP
I have modified the provided calculated column formula to this:
Time To First Act = VAR filtered = FILTER ( ADDCOLUMNS ( CROSSJOIN ( CALENDAR ( [Req Created Date], [First Acted Date] ), SELECTCOLUMNS ( GENERATESERIES ( 8, 17 ), "Hour", [Value] ) ), "Day of week", WEEKDAY ( [Date], 2 ) ), [Day of week] < 5 && [Workflow ID] = EARLIER ( Sheet1[Workflow ID] ) ) VAR hourcount = COUNTROWS ( FILTER ( filtered, ( [Date] >= DATEVALUE ( [Req Created Date] ) && [Hour] > HOUR ( [Req Created Date] ) + 1 ) && ( [Date] <= DATEVALUE ( [First Acted Date] ) && [Hour] > HOUR ( [First Acted Date] ) - 1 ) ) ) VAR remained = DATEDIFF ( TIMEVALUE ( [Req Created At] ), TIME ( HOUR ( [Req Created At] ) + 1, 0, 0 ), MINUTE ) + DATEDIFF ( TIME ( HOUR ( [First Acted At] ) - 1, 0, 0 ), TIMEVALUE ( [First Acted At] ), MINUTE ) RETURN IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )Thank you for any additional suggestions.
- bvbull2007 years agoHelper III
In case anyone finds this and attempts to solve, I have fully abandonded the idea of having Power BI do this calculation.
Power BI's inability to calculate net working days confounds me when it is such a simple formula in Excel.
As such, I have decided to do all calculations in Excel instead and import that data in to Power BI. This has unearthed another failure of Power BI, but I will provide it's own thread for that.
Staggering how powerful PBI is on many levels, yet is inept at relatively simple things.