Forum Discussion
Date Diff in Hours : Minutes excluding weekends..
Hi I was struggling to calculate the time difference in hours:minutes and have got a perfect solution and it is working perfectly.
Is it possible to exclude the weekends while using the below query?
Diff2 =
VAR DiffInMinutes =
DATEDIFF ( Table1[Start]; Table1[End]; MINUTE )
VAR DiffInHours =
QUOTIENT ( DiffInMinutes; 60 )
VAR ModuloDiffInMinutes =
MOD ( DiffInMinutes; 60 )
VAR Result =
FORMAT ( DiffInHours; "00" ) & ":"
& FORMAT ( ModuloDiffInMinutes; "00" )
RETURN
Result
Nolock Kindly help...
Hi Anonymous,
I've implemented your additional requirements for handling hours at weekends. You need some new columns:
First of all, you need to find a new Start timestamp. If it is Saturday or Sunday, just use the start of tomorrow.
NextPossibleStart = IF ( WEEKDAY ( Table1[Start]; 2 ) >= 6; DATEADD ( Table1[Start].[Date]; 1; DAY ); Table1[Start] )Then you need to find the start of a day if the end timestamps is Saturday or Sunday.
PreviousPossibleEnd = VAR IsWeekend = WEEKDAY ( Table1[End]; 2 ) >= 6 VAR NewEndDate = IF ( IsWeekend; Table1[End].[Date]; Table1[End] ) VAR IsNewEndDateBeforeStartDate = NewEndDate < Table1[NextPossibleStart] RETURN IF ( IsNewEndDateBeforeStartDate; Table1[NextPossibleStart]; NewEndDate )Use these 2 new columns in finding all weekend days between 2 dates.
CountOfWeekdays = VAR tableOfDays = CALENDAR ( Table1[NextPossibleStart]; Table1[PreviousPossibleEnd] ) VAR tableOfWeekdays = FILTER ( tableOfDays; WEEKDAY ( [Date]; 2 ) >= 6 ) VAR countOfWeekdays = COUNTROWS ( tableOfWeekdays ) RETURN IF ( ISBLANK ( countOfWeekdays ); 0; countOfWeekdays )And also in the diff:
DiffWithoutWeekends = VAR DiffInMinutes = DATEDIFF ( Table1[NextPossibleStart]; Table1[PreviousPossibleEnd]; MINUTE ) VAR DiffInHours = QUOTIENT ( DiffInMinutes; 60 ) VAR WeekendHours = 24 * Table1[CountOfWeekdays] VAR DiffInHoursWithoutWeekend = DiffInHours - WeekendHours VAR ModuloDiffInMinutes = MOD ( DiffInMinutes; 60 ) VAR Result = FORMAT ( DiffInHoursWithoutWeekend; "00" ) & ":" & FORMAT ( ModuloDiffInMinutes; "00" ) RETURN ResultSome tests:
And you can also download the PowerBI file again, I've uploaded the new version of it.
6 Replies
- Nolock
Resident Rockstar
Hi Anonymous,
I expect that neither start nor end timestamp is on the weekend. In the other case my code wouldn't work. If it can happen that start or end are at the weekend please explain what behavior you expect.
Create a new column which calculates all weekend days between A and B:
CountOfWeekdays = VAR tableOfDays = CALENDAR(Table1[Start]; Table1[End]) VAR tableOfWeekdays = FILTER(tableOfDays; WEEKDAY([Date]; 2) >= 6) VAR countOfWeekdays = COUNTROWS(tableOfWeekdays) RETURN IF(ISBLANK(countOfWeekdays); 0; countOfWeekdays)
Remove weekends from the difference:
DiffWithoutWeekends = VAR DiffInMinutes = DATEDIFF(Table1[Start]; Table1[End]; MINUTE) VAR DiffInHours = QUOTIENT(DiffInMinutes; 60) VAR WeekendHours = 24 * Table1[CountOfWeekdays] VAR DiffInHoursWithoutWeekend = DiffInHours - WeekendHours VAR ModuloDiffInMinutes = MOD(DiffInMinutes; 60) VAR Result = FORMAT(DiffInHoursWithoutWeekend; "00") & ":" & FORMAT(ModuloDiffInMinutes; "00") RETURN Result
And the result:
PowerBI file with the solution: PowerBI file
Thank you for replying.. This script is for calculating how much duration is taken to close an IT Ticket excluding weeknd hours.
If the Start falls in weekend then we should exclude the Start Time hours should be excluded while calculating the time difference. Similarly if the End Falls in weekend then End hours should be excluded while calculating the time difference.
Is this possible?
- Nolock
Hi Anonymous,
I've implemented your additional requirements for handling hours at weekends. You need some new columns:
First of all, you need to find a new Start timestamp. If it is Saturday or Sunday, just use the start of tomorrow.
NextPossibleStart = IF ( WEEKDAY ( Table1[Start]; 2 ) >= 6; DATEADD ( Table1[Start].[Date]; 1; DAY ); Table1[Start] )Then you need to find the start of a day if the end timestamps is Saturday or Sunday.
PreviousPossibleEnd = VAR IsWeekend = WEEKDAY ( Table1[End]; 2 ) >= 6 VAR NewEndDate = IF ( IsWeekend; Table1[End].[Date]; Table1[End] ) VAR IsNewEndDateBeforeStartDate = NewEndDate < Table1[NextPossibleStart] RETURN IF ( IsNewEndDateBeforeStartDate; Table1[NextPossibleStart]; NewEndDate )Use these 2 new columns in finding all weekend days between 2 dates.
CountOfWeekdays = VAR tableOfDays = CALENDAR ( Table1[NextPossibleStart]; Table1[PreviousPossibleEnd] ) VAR tableOfWeekdays = FILTER ( tableOfDays; WEEKDAY ( [Date]; 2 ) >= 6 ) VAR countOfWeekdays = COUNTROWS ( tableOfWeekdays ) RETURN IF ( ISBLANK ( countOfWeekdays ); 0; countOfWeekdays )And also in the diff:
DiffWithoutWeekends = VAR DiffInMinutes = DATEDIFF ( Table1[NextPossibleStart]; Table1[PreviousPossibleEnd]; MINUTE ) VAR DiffInHours = QUOTIENT ( DiffInMinutes; 60 ) VAR WeekendHours = 24 * Table1[CountOfWeekdays] VAR DiffInHoursWithoutWeekend = DiffInHours - WeekendHours VAR ModuloDiffInMinutes = MOD ( DiffInMinutes; 60 ) VAR Result = FORMAT ( DiffInHoursWithoutWeekend; "00" ) & ":" & FORMAT ( ModuloDiffInMinutes; "00" ) RETURN ResultSome tests:
And you can also download the PowerBI file again, I've uploaded the new version of it.