Difference between to DateTime columns (to the minute) for working days and time

[samhunter]

Hi, I have been struggling with this problem for a while now and have not been able to work it out or found a solution posted here (apologies if I have missed it). I can't believe I am the first to try and do this.

I would like to count the minutes between the 2 DateTime columns but only during working days and hours (e.g.08:30:00 to 05:30:00).

I have a table [Data] with 2 date columns [Open DateTime] and [Closed DateTime]. I have a calendar [Date] with working days [IsWorkingDay] (indicated by 1 & 0) and a time table [Time] with [IsWorkingTime] (indicated by 1 & 0) - time table is to the minute.

My approach have been to calculate the number of days in 1 column (by summing IsWorkingDay) and then the number of minutes (by summing IsWorkingTime) and then add the 2 columns together. This works except the time calculation fails when OpenTime is later than ClosedTime. 

The closes solution I have found is this one (https://community.powerbi.com/t5/Desktop/Calculate-Hours-between-dates-for-only-business-hours/td-p/...) but the volumn of data to get this down to the minute is massive so would like to try and find a solution using the lookup tables.

Anyone have any ideas?

[v-kelly-msft · Accepted solution]

Hi @samhunter ,

First split the date/time columns into 2 columns as date and time;

Then create a calulated column to get the date differences between open and close:

DATEDIFF = 
DATEDIFF('Table'[Date Open.1],'Table'[Date Closed.1],DAY)

After that create a measure as below:

Total minutes = 
var _open=HOUR(MAX('Table'[Date Open.2]))*60+MINUTE(MAX('Table'[Date Open.2]))
var _close=HOUR(MAX('Table'[Date Closed.2]))*60+MINUTE(MAX('Table'[Date Closed.2]))
var _notworkingtime=CALCULATE(COUNTROWS('Table (2)'),FILTER('Table (2)',('Table (2)'[Time]>=MAX('Table'[Date Open.2])&&'Table (2)'[Time]<=MAX('Table'[Date Closed.2])||MAX('Table (2)'[Time])<=MAX('Table'[Date Closed.2]))&&'Table (2)'[IsWorkingTime]=0))
var _notworkingday=CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2','Table 2'[Date]>=MAX('Table'[Date Open.1])&&'Table 2'[Date]<=MAX('Table'[Date Closed.1])&&'Table 2'[Column]=0))+0
Return
IF(MAX('Table'[DATEDIFF])<=1,24*60-_open+_close,(MAX('Table'[DATEDIFF])-1)*60-_open+_close-_notworkingtime-_notworkingday*60
)

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[samhunter]

Example tables

Date Open	Date Closed
24/02/2019 20:26	01/04/2020 17:33
15/08/2019 09:59	28/08/2019 17:03
15/08/2019 10:21	27/08/2019 15:58
19/08/2019 15:47	20/08/2019 10:16
19/08/2019 16:41	10/10/2019 12:28

Time	IsWorkingTime
00:00	0
00:01	0
00:02	0

Date	IsWorkingDay
23/02/2020	0
24/02/2020	1
25/02/2020	1
26/02/2020	1
27/02/2020	1

[v-kelly-msft · Accepted solution]

Hi @samhunter ,

First split the date/time columns into 2 columns as date and time;

Then create a calulated column to get the date differences between open and close:

DATEDIFF = 
DATEDIFF('Table'[Date Open.1],'Table'[Date Closed.1],DAY)

After that create a measure as below:

Total minutes = 
var _open=HOUR(MAX('Table'[Date Open.2]))*60+MINUTE(MAX('Table'[Date Open.2]))
var _close=HOUR(MAX('Table'[Date Closed.2]))*60+MINUTE(MAX('Table'[Date Closed.2]))
var _notworkingtime=CALCULATE(COUNTROWS('Table (2)'),FILTER('Table (2)',('Table (2)'[Time]>=MAX('Table'[Date Open.2])&&'Table (2)'[Time]<=MAX('Table'[Date Closed.2])||MAX('Table (2)'[Time])<=MAX('Table'[Date Closed.2]))&&'Table (2)'[IsWorkingTime]=0))
var _notworkingday=CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2','Table 2'[Date]>=MAX('Table'[Date Open.1])&&'Table 2'[Date]<=MAX('Table'[Date Closed.1])&&'Table 2'[Column]=0))+0
Return
IF(MAX('Table'[DATEDIFF])<=1,24*60-_open+_close,(MAX('Table'[DATEDIFF])-1)*60-_open+_close-_notworkingtime-_notworkingday*60
)

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[samhunter]

Hi Kelly

Thank you for your help. This feels very close (certainly the closest I've got so far). I think I may have misunderstood something.

For the second row in your table (No.12674 below), I expected 4152 Total Minutes but when I set this up I get 904 so something has gone wrong somewhere. I created measures from each of the variables in the Total Minutes measure and dropped them into a table giving me the numbers I was expecting for each of them. 

I can't get my head around the IF statement. I traced it through and found that if I add *24 in a couple of places to get the full days minutes I get 11944 (essentially what you got)

But its still not the 4152 I was expecting.

Any ideas?

[v-kelly-msft]

Hi @samhunter ，

How do you get the result of 4152, as what you have said in the original post,you wanna calculate out the total minutes during a certain period minus the notworkingdays and notworkingtime.

For instance,during 15/8/2019 9:59 to 28/8/2019 17:03,the total minutes equals 17704,workingdays equals 4,so the result should be 17704-4*24*60=11944,how do you get the result of 4152??

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[samhunter · Accepted solution]

@v-kelly-msft 

Sorry, my calculation was a little off. The logic I am using is that there are 541 minutes between 08:30 and 17:30, 10 working days so, with an adjustment for the start/finish times, the I think the total minutes should be 5294.

It looks like the notworkingtime hours variable is only counting from start to 08:30 and from 17:30 to finish on the first/last days which I think accounts for this difference. I could not work out how to change this, but I think I have been able to take what you have done and finish it off using a slightly different method. Its not pretty but it appears to work.

There are 541 working minutes in a day. Multiple that buy the number of working days in date range and then make an adjustment for the opened and closed time if either are within the working day.

Split date time columns into date and time columns

Measures:

NotWorkingDay = CALCULATE(COUNTROWS('Date'),FILTER('Date','Date'[Date]>=MAX('IT Incidents'[OpenedDate])&&'Date'[Date]<=MAX('IT Incidents'[ClosedDate])&&'Date'[IsWorkingday]=0))+0

OpenedTimeAdjustment = IF([open] > 511, [open]-511,0)             - 08:30 is the 511th minute of the day

ClosedTimeAdjustment = if([close]< 1051, 1051-[close],0)            - 17:30 is the 1051st minute of the day

Open = HOUR(MAX('IT Incidents'[OpenedTime]))*60+MINUTE(MAX('IT Incidents'[OpenedTime]))

Close = HOUR(MAX('IT Incidents'[ClosedTime]))*60+MINUTE(MAX('IT Incidents'[ClosedTime]))

DateDiff = DATEDIFF('IT Incidents'[OpenedDate],'IT Incidents'[ClosedDate],day)

Calculated column:

Total Minutes = ([DateDiff]-[notworkingday]+1)*541 - [OpenedTimeAdjustment] - [ClosedTimeAdjustment