measure date difference power excluding weekend

hi Dale

i tried but there some error can yu please check 

SLA Test

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 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

Hi v-jiascu-msft

sent to your privte inbox 

please check and help me to correct the formula

thanks 

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

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 

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

HI v-jiascu-msft

thank for this tips 

but what im lookiing for our noraml standard work time from 9 AM to 5 PM 

we have 1 month during the year we start at 9 AM and End on 3 PM 

how we can inculde this time in this month on main forumla 

or can we create table for Workday and include working time  ?? 

to make more easy lets us say on Jan our working time from 9 AM to 5 PM 

and Feb will be from 9 AM to 3 PM