Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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...
Solved! Go to Solution.
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 Result
Some tests:
And you can also download the PowerBI file again, I've uploaded the new version of it.
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?
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 Result
Some tests:
And you can also download the PowerBI file again, I've uploaded the new version of it.
Hi Nolock
I'm trying to download the PowerBI File with your solution to this topic but the download link does not work fo me. Can you kindly post the file again?
Kind regards
Bruno
You are amazingly awesome.. Thank you so much and working perfectly..
Hi all.
I have the need to exclude from the time difference in
Hours and Minutes Calculated here the non Office working hours. is there someone who can give me some help on how to do that?
WHstart and WHend could be a variable used to calculate that
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
115 | |
109 | |
74 | |
69 |