topic Re: SLA met and SLA Breached only for working days in DAX Commands and Tips

SLA met and SLA Breached only for working days

ashrat001 — Tue, 20 Dec 2022 10:46:40 GMT

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

Re: SLA met and SLA Breached only for working days

MAwwad — Tue, 20 Dec 2022 11:32:54 GMT

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.

Re: SLA met and SLA Breached only for working days

ashrat001 — Tue, 20 Dec 2022 12:19:47 GMT

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

Re: SLA met and SLA Breached only for working days

MAwwad — Fri, 06 Jan 2023 08:40:04 GMT

Normally that should not take Blanks into consideration

Re: SLA met and SLA Breached only for working days

ashrat001 — Tue, 20 Dec 2022 12:54:50 GMT

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

Re: SLA met and SLA Breached only for working days

ashrat001 — Tue, 20 Dec 2022 13:09:00 GMT

Hi,

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

Re: SLA met and SLA Breached only for working days

Anonymous — Fri, 06 Jan 2023 08:16:09 GMT

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