Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear all
apprecite your assist and support to found way measure date difference power excluding weekend
please note that i have start date/time & End date/time or i need to measure SLA for these dates/ time
moreover i have standred time 1h and 30 min to measure if employee has been Met SLA or not
our weekend are friday and saturday
our working hours from 8:00 AM till 5:00 PM
below are screen shot which i used to make on Excel but im planning now to make this report live to power BI
Solved! Go to Solution.
Hi @shado26,
How to override it? The formula in my last post will show up "Error" in the column.
ifmet = VAR sla2 = DATEDIFF ( DUMP_DATA_FROM_PURCHASE[start], DUMP_DATA_FROM_PURCHASE[end], SECOND ) RETURN IF ( WEEKDAY ( DUMP_DATA_FROM_PURCHASE[start],2 ) IN { 5, 6 }, "Week End approval", IF ( TIMEVALUE ( DUMP_DATA_FROM_PURCHASE[start] ) < TIME ( 9, 0, 0 ) || TIMEVALUE ( DUMP_DATA_FROM_PURCHASE[end] ) > TIME ( 18, 0, 0 ), "done", IF ( sla2 < 0,"Error",if(sla2 <= 5400, "Met", "Failed" ) ) ))
Best Regards,
Dale
kindly assist pleaseeeeeeeeeeeeeeeeeeeee
waiting for solutions
Dear all
apprecite your assist and support to found way measure date difference power excluding weekend
please note that i have start date/time & End date/time or i need to measure SLA for these dates/ time
moreover i have standred time 1h and 30 min to measure if employee has been Met SLA or not
our weekend are friday and saturday
our working hours from 8:00 AM till 5:00 PM
below are screen shot which i used to make on Excel but im planning now to make this report live to power BI
Hi @shado26,
What's the expected result? What kind of data do you have? The SLA could be:
SLA = CONCATENATE ( CONCATENATE ( CONCATENATE ( CONCATENATE ( CONCATENATE ( HOUR ( [Column1] ), " hours," ), MINUTE ( [Column1] ) ), " minutes," ), SECOND ( [Column1] ) ), "seconds" )
If you want to measure the values based on date, you can add a column like below.
Column = [Column1].[Date]
Best Regards,
Dale
thank for your reply
here screenshot from what i have
i have start date/time & End date/time and i need to measure SLA for these dates/ time
moreover i have standred SLA 1h and 30 min to measure if employee has been Met SLA or not
our weekend are friday and saturday
our working hours from 8:00 AM till 5:00 PM
Hi @shado26,
The formula of SLA could be like this. You can try it in this file.
SLA = VAR allseconds = DATEDIFF ( [Start], [End], SECOND ) VAR days = INT ( allseconds / 24 / 60 / 60 ) VAR hours = MOD ( INT ( allseconds / 60 / 60 ), 24 ) VAR minutes = MOD ( INT ( allseconds / 60 ), 60 ) VAR seconds = MOD ( allseconds, 60 ) RETURN days & " days " & hours & " hours " & minutes & " minutes " & seconds & " seconds"
I have to ask what is the expected result? Are the start date and the end date in one day? How many standard SLA? What are they?
Best Regards,
Dale
hellow Dale
thank for your feedback we have only one SLA which 1h 30 min
moreover i want to exclude weekend which " friday & stauerday " and exclude working hours which "9:00 AM to 6 PM "
Hi @shado26,
Can you post the full name of SLA? What does it stand for? Maybe the full name will help me understanding this scenario. The related question is how to determine if the time meets SLA?
Another question: do you mean the end date time minus the start date time and excluding the weekends and non-working hours?
For example:
2018-01-01 is Monday. So 2018-01-01 9:00:00, 2018-01-01 10:00:00, the SLA is 1 hours;
2018-01-05 is Friday. So the SLA is 0;
Best Regards,
Dale
hi dale
SLA mean service level agreement
our stranded SLA are 1h 30 min to process orders
so if i received any order i need to process it within 1h 30 min
so i need formula to calculate received time " DateTimeFrom" and approved time " DateTimeTo" and to exclude week and working hours
Ex i approved this with 18 Min
Hi @shado26,
Please try out a calculated column like this. You can download a demo here.
ifmet = VAR sla2 = DATEDIFF ( [Start], [End], SECOND ) RETURN IF ( WEEKDAY ( [Start], 2 ) IN { 5, 6 }, "NA", IF ( TIMEVALUE ( [Start] ) < TIME ( 8, 0, 0 ) || TIMEVALUE ( [End] ) > TIME ( 17, 0, 0 ), "NA2", IF ( sla2 <= 5400, "Met", "Failed" ) ) )
Notes:
All the signs can be changed.
NA: weekends.
NA2: either start or end is out of work hour.
Best Regards,
Dale
Hi @shado26,
I hope your data isn't classified. Do you mean there are time intervals smaller than 0? They are from your original data. I changed the formula to find them out. How do you want to deal with them?
ifmet = VAR sla2 = DATEDIFF ( DUMP_DATA_FROM_PURCHASE[start], DUMP_DATA_FROM_PURCHASE[end], SECOND ) RETURN IF ( WEEKDAY ( DUMP_DATA_FROM_PURCHASE[start],2 ) IN { 5, 6 }, "Week End approval", IF ( TIMEVALUE ( DUMP_DATA_FROM_PURCHASE[start] ) < TIME ( 9, 0, 0 ) || TIMEVALUE ( DUMP_DATA_FROM_PURCHASE[end] ) > TIME ( 18, 0, 0 ), "done", IF ( sla2 < 0,"Error",if(sla2 <= 5400, "Met", "Failed" ) ) ))
If your data is confidential, please mask them first.
Best Regards,
Dale
Hi Dale
I got where this error came from
I found some received date is after approved date
Like the below this are approved on 10 AM and alert are received on 12 PM
can we have IF to overright this
Hi @shado26,
How to override it? The formula in my last post will show up "Error" in the column.
ifmet = VAR sla2 = DATEDIFF ( DUMP_DATA_FROM_PURCHASE[start], DUMP_DATA_FROM_PURCHASE[end], SECOND ) RETURN IF ( WEEKDAY ( DUMP_DATA_FROM_PURCHASE[start],2 ) IN { 5, 6 }, "Week End approval", IF ( TIMEVALUE ( DUMP_DATA_FROM_PURCHASE[start] ) < TIME ( 9, 0, 0 ) || TIMEVALUE ( DUMP_DATA_FROM_PURCHASE[end] ) > TIME ( 18, 0, 0 ), "done", IF ( sla2 < 0,"Error",if(sla2 <= 5400, "Met", "Failed" ) ) ))
Best Regards,
Dale
i tried your forumla but its show all error
and its always mention the below
In DATEDIFF function, the start date cannot be greater than the end date
sorry to bothering you
on this can you make test and send to me
Hi @shado26,
Please upgrade your Desktop to the latest version from https://www.microsoft.com/en-us/download/details.aspx?id=45331. Then you can use the formula freely. (No error about Datediff)
The question is how to define weekend when start and end aren't in a single day.
Best Regards,
Dale
i want to thank you to help me on this its work fine now after instal the new version of Power PI
i have one question to ask
can we add on this to calculate the SLA from working hours
like Ex
if i received order on 5:30 PM
and approved next working day on 9:30
i calculation of SLA should be 30 min in evening and 30 min in morning to this will be met the SLA
another Ex
if i received order on 5:30 PM
but approved next working day on 10:30
this SLA for this will be 2 h for this should count as faild
many thanks
shadi
Hi @shado26,
I have a similar case today. You can add a new column to computer the work hours only. Please check the demo here.
AllInOne = VAR standardStart = IF ( TIMEVALUE ( [Start Time] ) < TIME ( 9, 0, 0 ), DATEVALUE ( [Start Time] ) + TIME ( 9, 0, 0 ), IF ( TIMEVALUE ( [Start Time] ) > TIME ( 17, 0, 0 ), DATEVALUE ( [Start Time] ) + TIME ( 17, 0, 0 ), [Start Time] ) ) VAR standardEnd = IF ( TIMEVALUE ( [End Time] ) < TIME ( 9, 0, 0 ), DATEVALUE ( [End Time] ) + TIME ( 9, 0, 0 ), IF ( TIMEVALUE ( [End Time] ) > TIME ( 17, 0, 0 ), DATEVALUE ( [End Time] ) + TIME ( 17, 0, 0 ), [End Time] ) ) VAR businessDay = CALCULATE ( SUMX ( DateTable, [IsWorkday] ), FILTER ( 'DateTable', 'DateTable'[Date] > [Start Time].[Date] && 'DateTable'[Date] < [End Time].[Date] ) ) RETURN IF ( [Start Time] > [End Time], 9999, IF ( [Start Time].[Date] = [End Time].[Date], DATEDIFF ( standardstart, standardend, HOUR ), [Business Days] * 8 + DATEDIFF ( TIMEVALUE ( standardstart ), TIME ( 17, 0, 0 ), HOUR ) + DATEDIFF ( TIME ( 9, 0, 0 ), TIMEVALUE ( standardend ), HOUR ) ) )
Best Regards,
Dale