Forum Discussion

busy86's avatar
busy86
Frequent Visitor
8 years ago

DATEDIFF Working Days MTTR

Hi there,

 

I'm just finding my feet in PowerBI and am currently tasked with building a dashboard for our servicedesk application and have got to the point where I need to calculate mean time to repair (basically created date - resolved date = days to repair) however to account for working days only. I have spent hours searching on here for a solution and have found a few examples, but I'm struggling to piece one together.

 

So far, I have a date table which has the following columns: Date, Day, Weekday. Weekday is a "1" for Mondays to Friday, and a "0" for Saturday and Sunday.

 

My main database table has a calculated column (Cal_TTR) as follows:

 

Cal_TTR = DATEDIFF('table'[created_on].[Date],'table'[resolved_date].[Date],DAY)

 

This gives me the whole number of days between created and resolved, but I need working days only.

Even better would be calculation down to the minute between 9am-5pm on working days only.

 

How can this be achieved?

 

Many thanks in advance.

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    If you have a Date table with those columns, why not create a measure something like:

     

    Cal_TTR = CALCULATE(SUM(Dates[Weekday]),FILTER(Dates,Dates[Date]>='table'[created_on].[Date] && Dates[Date]<='table'[resolved_date].[Date]))
    • busy86's avatar
      busy86
      Frequent Visitor
      8 years ago

      Thanks that has done the job. Do you know how I can expand this to cover just working hours on those weekdays? Currently, if a ticket is resolved the same day the result is 0 which skews the average somewhat.

       

      Many thanks.