Forum Discussion

jj1's avatar
jj1
Icon for Helper II rankHelper II
2 years ago

bus day sla dax

  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

 

 

DAX Time Intelligence

6 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      jj1
      Icon for Helper II rankHelper II
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.