Calculation time difference between two time values excluding weekends

[EddieTran]

Hi guys,

I have trouble with calculating the difference in time between current time ( NOW () ) and a specific column (called as Created Date), excluding the weekends (and maybe the holidays as well, but it's ok to start with the weekends)

• I have tried DATEDIFF function to calculate the time, it worked fine but its drawback was it included the weekend which made me really hard to conduct some reports regarding SLAs.

• I also tried DATESBETWEEN as well (as followed: http://community.powerbi.com/t5/Desktop/Date-Duration-exclude-weekends/td-p/87735), but again, the drawback of this function is: it counts the day passed (even it excluded the weekends and holidays). For example: I want to know the time difference between 19/11/2017 15:30 and 20/11/2017 15:30 (which is 1), whereas, the function counts the number of days showing up: 19/11 and 20/11 (which is 2).

Thank you guys!! Hope to hear from you soon!!

[Eric_Zhang]

---

@EddieTran wrote:

Hi guys,

I have trouble with calculating the difference in time between current time ( NOW () ) and a specific column (called as Created Date), excluding the weekends (and maybe the holidays as well, but it's ok to start with the weekends)

• I have tried DATEDIFF function to calculate the time, it worked fine but its drawback was it included the weekend which made me really hard to conduct some reports regarding SLAs.

• I also tried DATESBETWEEN as well (as followed: http://community.powerbi.com/t5/Desktop/Date-Duration-exclude-weekends/td-p/87735), but again, the drawback of this function is: it counts the day passed (even it excluded the weekends and holidays). For example: I want to know the time difference between 19/11/2017 15:30 and 20/11/2017 15:30 (which is 1), whereas, the function counts the number of days showing up: 19/11 and 20/11 (which is 2).

Thank you guys!! Hope to hear from you soon!!

---

@EddieTran

I don't understand your situation. Say the created dates locate in weekends, isn't the SLA calculated as NOW() - next Monday 00:00:00? In you example, the created data is on a Sunday(19/11/2017), what if 18/11/2017? what is the expected difference, 1 or 2?

[EddieTran]

@Eric_Zhang

Oh, I'm sorry for not being clear enough

Let's say we calculate the SLA by NOW () - Created time as you can see in the example (which turns out as a number like 1 day or 2 days or 3 days, etc...)

Let me give you a specific example of my case:

Today is 4:00 pm 22.11.2017 and the created time is 4:00 pm 16.11.2017. If I use the DATEDIFF Function, the result turns out to be 6 (including Saturday and Sunday), whereas if I use the DATESBETWEEN function (excluding Saturday and Sunday - I found way to do that in the example), it will count the number of days: 5 (Thursday, Friday, Monday, Tuesday and Wednesday).

However. my expected result is 4 (which is from 4:00 pm 16.11 to 4:00 pm 17.11, 4 p.m 17.11 to 4 p.m 20.11 as 1 and so on). The data is always created on weekdays. All I want to do is to subtract the time and exclude the weekends. Thank you!!

[Anonymous]

This might just be a very silly suggestion, but in your second point, can't you just always subtract 1? In this case, it will always count the current day, so subtracting that day would leave you with the answer you seek, right?

[EddieTran]

@Anonymous

I have tried this too, it looks much cooler but there is a problem with the report showing up like the example down there. For example, if today time is 5:00 22.11.2017 and the created time is 4:00 22.11.2017. It will show up on the "Day passed" column as -1. It would be weird because an hour has passed but it shows as negative 1 day. Thank you for your suggestion!!

[Anonymous]

Hey Eddie,


The thing I find a bit mysterious is that if the created and now are both today, your measure gives you a 0 in response, whereas if the created date and now differ by one day, you get a 2, correct? I dont understand this behaviour 😞

In your first post you write this:

"For example: I want to know the time difference between 19/11/2017 15:30 and 20/11/2017 15:30 (which is 1), whereas, the function counts the number of days showing up: 19/11 and 20/11 (which is 2)." By that logic, if your createddate and Today are on the same day, it should give you a 1. Then you subtract 1 and end up with a 0, which is the correct answer.

 I think I'm not understanding the measure you created. It seems to behave very randomly 😛

I think we need to go back to the beginning. What do you want the behaviour to be? If I create a ticket at 23:00 on day 1, and NOW is the next day at 1:00 AM, Do you want to see a 0, a 1 or a 2?

[EddieTran]

"The thing I find a bit mysterious is that if the created and now are both today, your measure gives you a 0 in response, whereas if the created date and now differ by one day, you get a 2, correct?" Yes, that's what's happening right now with the measure. I believe that I have made some mistake with the behaviour 

My measure is: DaysPassed = CALCULATE(SUM('Calendar'[IsWorkDay]),
DATESBETWEEN('Calendar'[Date], 'BPM_VNDS_Report SYS_WorkflowSession'[CreateDate], TODAY())
)

which is to count the number of days excluding the weekends.

"For example: I want to know the time difference between 19/11/2017 15:30 and 20/11/2017 15:30 (which is 1), whereas, the function counts the number of days showing up: 19/11 and 20/11 (which is 2)." By that logic, if your createddate and Today are on the same day, it should give you a 1. Then you subtract 1 and end up with a 0, which is the correct answer.

 I think I'm not understanding the measure you created. It seems to behave very randomly  => I totally agree with you on this, I still don't understand why 

I think we need to go back to the beginning. What do you want the behaviour to be? If I create a ticket at 23:00 on day 1, and NOW is the next day at 1:00 AM, Do you want to see a 0, a 1 or a 2? => It's only 2 hours from 23:00 day 1 to 1:00 AM day 2, so my expected results should be 0 day. (Exactly it should be 1/12 day, but it's ok to be 0)