Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ashrat001
Helper I
Helper I

SLA met and SLA Breached only for working days

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

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @ashrat001 ,

 

 

Here are the steps you can follow:

1. Create calculated table.

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

vyangliumsft_0-1672992893373.png

2. Create calculated column.

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

vyangliumsft_1-1672992893374.png

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:

vyangliumsft_2-1672992893375.png

 

 

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

View solution in original post

6 REPLIES 6
v-yangliu-msft
Community Support
Community Support

Hi  @ashrat001 ,

 

 

Here are the steps you can follow:

1. Create calculated table.

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

vyangliumsft_0-1672992893373.png

2. Create calculated column.

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

vyangliumsft_1-1672992893374.png

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:

vyangliumsft_2-1672992893375.png

 

 

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

Hi,

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

MAwwad
Super User
Super User

 

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.

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

Normally that should not take Blanks into consideration

 

 

 

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

PR to PO.PNG

 




 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.