- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculate Date and Time difference considering the weekends and workhours
Hi everybody!
I'm still learning how to use Power BI, I search for this everywhere but I didn't found an answer!
Here is the thing,
I need to calculate the difference between dates and time, but the thing is that I need to calculate just the working days and the workhours.
Taking the first line as an example: (03/11/2016 13:57:22 - 03/01/201613:36:38) the column hours should be 63:38:38
Considering that 03/05 and 06/05 are saturday and sunday, and considering that the work hours are from 08:00AM to 18:00PM.
How can I solve that!
Thank you!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The only possibility I can think of is that there is something wrong with the Calendar'[WorkDay] formula. Could you please check this column formula is like below? The data type of this column should be “True/False”.
If it is actually same as mine, could you please upload your .pbix file to OneDrive and share it with me? In that case I can take a look at your .pbix file and try to solve the problem.
Best Regards,
Herbert
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How about the result if we update the “FirstDaySecDiff” measure as below?
FirstDaySecDiff = IF ( Table1[FirstDayEndTime] >= Table1[DateTimeFrom] && Table1[FirstDayEndTime] <= Table1[DateTimeTo], DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND ), IF ( Table1[FirstDayEndTime] >= Table1[DateTimeFrom] && Table1[FirstDayEndTime] > Table1[DateTimeTo], DATEDIFF ( Table1[DateTimeFrom], Table1[DateTimeTo], SECOND ), 0 ) )
Best Regards,
Herbert
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Herbert,
Thanks again for your help and sorry for bothering you again,
The last problem was solved, but I still have a problem with the MidDaysSecDiff column.
As you can see on the screen below, It gives an erro saying that DAX can't compare date values with True/False values.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It seems there are extra expression in your formula screenshot as below. How about the result if you remove it?
If problem persits, could you please give a complete screenshot for this MidDaysSecDiff formula?
Best Regards,
Herbert
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Herbert!
I removed this line but the problem still remains!
I compared the both codes and they are the same, as you can see below
MidDaysSecDiff =
IF (
FORMAT ( [DateTimeFrom]; "Short Date" )
<> FORMAT ( [DateTimeTo]; "Short Date" );
3600 * 10
* (
CALCULATE (
DISTINCTCOUNT ( 'Calendar'[Date] );
FILTER (
'Calendar';
'Calendar'[Date] > [FirstDayEndTime]
&& 'Calendar'[Date] < [LastDayStartTime]
&& 'Calendar'[WorkDay] = TRUE ()
)
)
- 1
);
0
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The only possibility I can think of is that there is something wrong with the Calendar'[WorkDay] formula. Could you please check this column formula is like below? The data type of this column should be “True/False”.
If it is actually same as mine, could you please upload your .pbix file to OneDrive and share it with me? In that case I can take a look at your .pbix file and try to solve the problem.
Best Regards,
Herbert
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have been working with this code, but is possible agree a new column to limit the TimeFrom date, and it can considering to the final result in hours
For example:
TimeFrom | TimeTo |
01/01/2018 07:00 | 01/01/2018 10:00 |
My Result is 3 hours, but it should be 2 hours, considering the business hours in the day 08:00 am to 18:00 pm, May be I must agree a column like the LastDayStartTime, but using the First Star Time.
Some Insight?
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have the same issue, wondering if we are missing a FirstDayStartTime, if the starting time is before 8am (7:00:00AM) and finish time is 10:00:00 AM is giving me has result the regular time diff, = 3hrs
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello guys.
Is there a way to add a function that excludes the holidays and weekends?
Thank you so much
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Herbert,
I need a little help here again.
I tried to solve it but I couldn't find an solution.
As you can see on the picture bellow, the calculation between two dates is correclty just when the date is different. But when the difference is just between hours the result is the DateTimFrom column - FirstDayEndTime Column.
For example:
03/01/2016 10:50:42 = 7,155 - but the result should be 2,046
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How about the result if we update the “FirstDaySecDiff” measure as below?
FirstDaySecDiff = IF ( Table1[FirstDayEndTime] >= Table1[DateTimeFrom] && Table1[FirstDayEndTime] <= Table1[DateTimeTo], DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND ), IF ( Table1[FirstDayEndTime] >= Table1[DateTimeFrom] && Table1[FirstDayEndTime] > Table1[DateTimeTo], DATEDIFF ( Table1[DateTimeFrom], Table1[DateTimeTo], SECOND ), 0 ) )
Best Regards,
Herbert
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Herbert, can you provide some more asissaance here?
the forumla doesnt work if the firstdate is before the SLA time,
for example if the DateTimeFrom is 7AM and the SLA start time is 9AM and the finish time is 10AM the working hours should be 1 but the current calculation = 3.
We need to add in a new Calculated column called
FirstdayStartTime
FirstDayStartTime =
DATE ( YEAR ( Table1[DateTimeFrom] ), MONTH ( Table1[DateTimeFrom] ), DAY ( Table1[DateTimeFrom] ) )
& " 09:00:00"
So therefore the FirstDaySecDiff would have to be modified to include this from;
IF (
Table1[FirstDayEndTime] >= Table1[DateTimeFrom]
&& Table1[FirstDayEndTime] <= Table1[DateTimeTo],
DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND ),
IF (
Table1[FirstDayEndTime] >= Table1[DateTimeFrom]
&& Table1[FirstDayEndTime] > Table1[DateTimeTo],
DATEDIFF ( Table1[DateTimeFrom], Table1[DateTimeTo], SECOND ),
0
)
))
to something that includes FirstDayStartTime - please can you help?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi guys!
I have made a few tweaks to @v-haibl-msft file, see here my updated version .
I had issues with some posts;
- A post which was received before openingtime on the same day (LastDayStartTime) for example at 02:00 AM and was picked up at 7:15 AM (My LastDayStarttime is 07:00 AM) then the response time should be 15 minutes. I have added a column FriDayEndMidNight which checks if the post was received on the same day but before LastDayStartTime (I added calculations in the FirstDaySecDiff Column). This now works.
- I also wanted to include Public Holidays. Sometimes my customer care teams are closed for example on a Monday or Tuesday and it should skip those days in the calculations. So I have loaded in an Excel sheet in which I enter the Holidays (see table 'Holidays' in PBI) and related that table to the Calendar table. Then I check in the WorkDay column in Calendar if the date exists in the Holidays file, if TRUE then Return FALSE.
- Then I have made some changes to the FirstDaySecDiff column to skip calculated the diff if WorkDay is False.
- I also made a relationship between DateTimeFrom and Calendar so I could use the related value.
- Furthermore sometimes my team picks up a post outside workinghours, I feel like I need to draw the line there so I won't calculate response time over those posts. However I have added a column in my table that checks whether a post was picked up inside or outside working hours, so I can apply that as a filter to my graphs.
Thanks again Herbert! My DAX skills are quite limited so your file really helped me out.
I know some people had issues with posts published before openingtime on the same day, this file should help.
Thanks!
Maartje
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, thanks for your help. i just cant make this work if the time difference between the start date and end date is over 7 days
for example, if i use:
First Day Start Time 09:00, First Day End Time :17:30
Last Day Start Time 09:00, Last Day End Time :17:30
If i use two examples, neither calculate properly:
Datetimefrom 22/6/2018 17:25
DateTimeTo 25/6/2018 09:10
Expected difference 15 minutes, TotsalMinDiff = 10 mins
Datetimefrom 18/6/2018 13:05
DateTimeTo 27/6/2018 17:05
Expected difference 3810 minutes, TotsalMinDiff = 4085 mins
do you see a similar pattern?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @ballist1x,
What are the working days from your team? Is it 7 days a week 09:00 - 17:30?
Let me know and I will have a look.
Thanks,
M
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
we got it sorted in the end, but had to take into account several other parameters as well. For example, an event could start at weekend and alos be actioned by the team at weekend our outside of SLA hours so the firstdaysec diff would be more like:
FirstDaySecDiff = IFERROR(
IF( //Created on Weekend, Assigned after 9AM Monday
'2018'[CreatedWorkDay] = FALSE()
&& '2018'[Assigned] > '2018'[FollowingMondayStartTime],0,
IF( //Created on Weekend, Assigned before 9AM Monday
'2018'[CreatedWorkDay] = FALSE()
&& '2018'[Assigned] < '2018'[FollowingMondayStartTime], 0,
IF( //Created on Weekend, Assigned on Weekend
'2018'[CreatedWorkDay] = FALSE()
&& '2018'[AssignedWorkDay] = FALSE(), 0,
IF(
'2018'[CreatedWorkDay] = FALSE()
&& '2018'[Created] < '2018'[FirstDayStartTime],
DATEDIFF('2018'[FirstDayStartTime], '2018'[Assigned], SECOND),
IF(
'2018'[FirstDayEndTime] >= '2018'[Created]
&& '2018'[FirstDayEndTime] <= '2018'[Assigned],
DATEDIFF ( '2018'[Created], '2018'[FirstDayEndTime], SECOND ),
IF (
'2018'[FirstDayEndTime] >= '2018'[Created]
&& '2018'[FirstDayEndTime] > '2018'[Assigned],
DATEDIFF ( '2018'[Created], '2018'[Assigned], SECOND ),
0
)))))),
0)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @maartjedutchy,
I've tried your solution but it doesn't work properly.
Service window = Workdays from 07:30 to 17:00 (NL-time)
I have the following item:
Date/Time ticket created = 6/7/2018 on 9:31
Date/Time ticket closed = 11/7 on 11:22
Calculation by hand:
6/7 - 7 hours, 29 minutes (9:31 - 17:00)
7/7 - 0 hours
8/7 - 0 hours
9/7 - 9 hours, 30 minutes (7:30-17:00)
10/7 - 9 hours, 30 minutes (7:30-17:00)
11/7 - 3 hours, 52 minutes (7:30-11:22)
Total is 30 hours, 21minutes or 1821 minutes
Your calculation give me a TotalMinDiff of 1432. See attached picture.
Something seems off, any idea?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
i think i found out what's wrong here:
- The middayssecdiff has the servicehours hard-coded (3600 * 10). My window is 9,5 hours, by changing it to 3600 * 9.5 that was allright and got a small deviation fixed.
- The formula for FirstDaySecDiff produces 0 when the ticket hasn't been resolved the same day. That's wrong.
The formula in your file:
IF (
'SLA'[FirstDayEndTime] >= 'SLA'[DateTimeFrom]
&& 'SLA'[FirstDayEndTime] <= 'SLA'[DateTimeTo] && RELATED('Calendar'[WorkDay]) = TRUE ();
DATEDIFF ( 'SLA'[DateTimeFrom]; 'SLA'[FirstDayEndTime]; SECOND );
Fails on the RELATED('Calendar'[WorkDay]) = TRUE ();
When turning that off, it works. But it doesn't when a ticket got created in the weekend. As we speak i'm checking to fix that.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I changed a view things to work with regionalization a little bit easier.
In the time calculations dont use & "18:00:00" because then PowerBI has to interpret a text to a date which is depending on the regional settings. Instead just add the time as datetime object like so: + TIME(18,0,0).
When you do it like that you will always receive an datetime object and its easy (and propably faster) to interpret for PowerBI.
I also used a public holiday calendar and imported it to another table and created a * to Many relationship between the calendar.date and the hollidays.date. My Calculated Column in Calendar now checks if it was a holiday:
Holliday = CALCULATE( MIN(Hollidays[HollidayName]), ALLEXCEPT('Calendar','Calendar'[Date]))
and then you can check if ( isblank(holliday)) which will give you if the date was a holiday or not. I added to the workday check if the day was a holliday or not.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Herbert! You are a freaking LEGEND!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much for sharing! This is exactly what I was needing.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thumbs up. This was exactly what I needed also. Thank you for contributing!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Problem Solved!
Thanks again Herbert!
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
03-27-2024 04:17 AM | |||
Anonymous
| 11-08-2023 03:28 AM | ||
Anonymous
| 08-03-2023 07:36 AM | ||
Anonymous
| 02-26-2024 03:46 AM | ||
03-18-2024 11:08 AM |
User | Count |
---|---|
126 | |
80 | |
59 | |
57 | |
43 |
User | Count |
---|---|
183 | |
111 | |
82 | |
65 | |
50 |