measure date difference power excluding weekend

[shado26]

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 

https://imgur.com/a/FVAMt

[v-jiascu-msft · Accepted 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

[shado26]

kindly assist pleaseeeeeeeeeeeeeeeeeeeee

[shado26]

waiting for solutions

[shado26]

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 

https://imgur.com/a/FVAMt

[v-jiascu-msft]

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

[shado26]

Hi @v-jiascu-msft

thank for your reply 

here screenshot from what i have 

https://imgur.com/a/FVAMt

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 

[v-jiascu-msft]

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

[shado26]

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 "

[v-jiascu-msft]

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

[shado26]

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 

[v-jiascu-msft]

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

[shado26]

hi Dale

i tried but there some error can yu please check 

SLA Test

[v-jiascu-msft]

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

[shado26]

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 

[v-jiascu-msft · Accepted 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

[shado26]

HI @v-jiascu-msft

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

[v-jiascu-msft · Accepted solution]

Hi @shado26,

You can download the file from here. Can you share your file please?

Best Regards,

Dale

[shado26]

Hi @v-jiascu-msft

sent to your privte inbox 

please check and help me to correct the formula

thanks 

[v-jiascu-msft]

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

[shado26]

Hi @v-jiascu-msft

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 

[v-jiascu-msft]

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