SLA met and SLA Breached only for working days

[ashrat001]

Hi Experts,

I have PR created dates and PR Final Approval dates. I need to calculate SLA Met measure having only working days. Say I have PR created date 1-Jan-2022 and PR Final Approval date 6-Jan-2022. I need to calcuate only working number of days that took the final approval and if the date difference is 5 or less then SLA Met and if the different is greater than 5 then it will be SLA Breached.

What's the best way to approach and calculate these measures? 

Thanks!

TA

[v-yangliu-msft · Accepted solution]

Hi  @ashrat001 ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
CALENDAR(
    DATE(2022,1,1),DATE(2022,12,31))

2. Create calculated column.

Weekday = WEEKDAY('Table 2'[Date],2)

3. Create measure.

WorkDay =
var _table=
FILTER(ALL('Table 2'),NOT('Table 2'[Weekday]) in {6,7})
return
COUNTX(
FILTER(ALL('Table 2'),
'Table 2'[Date]>=MAX('Table'[PR Final Approval])&&'Table 2'[Date]<=MAX('Table'[Assignment Date])&&NOT('Table 2'[Weekday]) in {6,7}),[Date])

Flag =
 IF(
     [WorkDay] <=5,"SLA Met","SLA Breached")

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ashrat001]

Hi,

   I think filter is not working for some reason. Thanks!

[MAwwad]

To calculate the number of working days between two dates in Power BI, you can use a combination of the DATEDIFF function, the CALENDAR function, and the FILTER function.

Here is an example of how you could create a measure to calculate the number of working days between the "PR Created Date" and the "PR Final Approval Date":

Copy code

Working Days = VAR start_date = MIN('Table'[PR Created Date]) VAR end_date = MAX('Table'[PR Final Approval Date]) VAR calendar = CALENDAR(start_date, end_date) RETURN CALCULATE( DATEDIFF(start_date, end_date, DAY), FILTER(calendar, WEEKDAY(calendar[Date], 2) < 6) )

This measure will use the DATEDIFF function to calculate the number of days between the start date and the end date, and the FILTER function to exclude weekends (days with a weekday value of 6 or 7) from the calculation. The resulting value will be the number of working days between the two dates.

You can then use the IF function to compare the number of working days to a threshold value and determine whether the SLA was met or breached. For example:

Copy code

SLA Met = IF( [Working Days] <= 5, 1, 0 )

This measure will return a value of 1 if the number of working days is 5 or less, and 0 if the number of working days is greater than 5.

[ashrat001]

Hi,

    Thanks for your prompt reply. Will that cater blank dates for PR Created Date or PR Final Approval Date in this formula?

Thanks!

TA

[MAwwad]

Normally that should not take Blanks into consideration

[ashrat001]

Hi,

Below code is not ignoring weekends: 

Working Days =

VAR start_date = MIN('PH - Purchase Order Items'[PR Final Approval On.PR Final Approval On Level 01])

VAR end_date = MAX('PH - Purchase Order Items'[Assignment Date.Assignment Date Level 01])

VAR calendarv = CALENDAR(start_date, end_date)

RETURN

CALCULATE( DATEDIFF(start_date, end_date, DAY), FILTER(calendarv, WEEKDAY(calendarv, 2) < 6) )