bus day sla dax

[jj1]

1. hi i have the following tables-but cant obtain bus day sla it seems-
2. i have obtained calendar day sla to measure days(that part is working and giving me my data)  but i need to go further and now convert to business days sla between same two columns
3. i also created a holiday table and a weekday value table-what is best formula here to convert calendar day sla to bus day sla? below is the calendar day sla that is working and the two tables being compared on days- 

additionally i created these two tables-weekday value 1-7 

holiday table

[Anonymous]

Hi All
Firstly  lbendlin thank you for your solution!
And @jj1 for you problem,as I understand it, you have now solved how to calculate the number of days between the two columns, but you now want to exclude holiday time from those days right?

We can first create a date table, and then each date in the date table to determine the day of the week they belong to apply to our filter conditions.

DataTable = CALENDAR(MIN('basic_sla_data'[createdon]),MAX('basic_sla_data'[actualend]))

week value = WEEKDAY('DataTable'[Date].[Date],2)

Business Days SlA = 
VAR A=MAX('basic_sla_data'[createdon])
VAR B=MAX('basic_sla_data'[actualend])
VAR C=CALCULATE(COUNTROWS('DataTable'),'DataTable'[Date]>=A&&'DataTable'[Date]<=B,'DataTable'[week value]<=5&&NOT('DataTable'[Date] IN VALUES('holiday_data'[Date])))
RETURN
C

If you still have questions, check out the pbix file I uploaded, I hope it helps, and I'd be honoured if my method solves your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[jj1]

hi thanks-unfortunately using that  formula but inserting my table names yielded a blank value in  bus day sla

[Anonymous]

Hi @jj1 ,
Because I use the measure, not calculated columns, so there may be errors, if you want to use calculated columns, you can remove the aggregation function, such as MAX, you can get the effect you need, but due to the calculated columns can only read a table in the data, cross-table use will show a blank value, you can use the calculated columns in the basic data, you can get the needs you want! You can use the calculated column in basic data to get the requirement you want.

Business Days SlA(column) = 
VAR A=('basic_sla_data'[createdon])
VAR B=('basic_sla_data'[actualend])
VAR C=CALCULATE(COUNTROWS('DataTable'),'DataTable'[Date]>=A&&'DataTable'[Date]<=B,'DataTable'[week value]<=5&&NOT('DataTable'[Date] IN VALUES('holiday_data'[Date])))
RETURN
C

I hope my answer will help to solve your doubts, I will be honoured!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[jj1]

yes almost at goal now- calculate worked give im using columns but ad you said will be blank value if cal day sla is 0-so goal is to get not just cal day sla( i have that or bus day sla( have that) but also lastly avg bus day sla -which cant be done accurately with blank value-so any ideas?

[Anonymous]

Hi @jj1 ,
If blank values are affecting your calculations, then we can add some judgement to the original formula to ensure that our Bus sla does not display blank values and calculates them as 0 instead, so that blank values are not excluded from the calculation of average, ensuring the accuracy of your calculations.

Business Days SlA(column) = 
VAR A=('basic_sla_data'[createdon])
VAR B=('basic_sla_data'[actualend])
VAR C=IF('basic_sla_data'[SLA]<>0&&'basic_sla_data'[SLA]<>BLANK(),
CALCULATE(COUNTROWS('DataTable'),'DataTable'[Date]>=A&&'DataTable'[Date]<=B,'DataTable'[week value]<=5&&NOT('DataTable'[Date] IN VALUES('holiday_data'[Date]))),
0)

RETURN
C

I hope my solution will be helpful to you!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[lbendlin]

Now that you have prepared the weekday information and the Holiday table you can use the NETWORKDAYS function which accepts these data points.