Forum Discussion

anusornkhurana's avatar
anusornkhurana
Regular Visitor
3 years ago

Time Filter Not Working

Hi, 

In another thread , Creating Time Filter, I was looking for a solution to filter First Response Time (Mins) according to create date

 

I wanted to know the average First response time (mins) that were created during working hours 08:00 - 17:00

and average First response time (mins) that were created outside working hours 17:01 - 7:59

 

In my previous thread, Anonymous, provided a solution by creating a measure

 

During Working Hours

average(8:00-17:00) = var _a=CALCULATE(SUM('Table'[First Response (Minute)]),FILTER('Table', 'Table'[Create Time]<=TIME(17,00,00) && 'Table'[Create Time]>=TIME(8,00,00)))
var _b=COUNTROWS(FILTER('Table','Table'[Create Time]<=TIME(17,00,00) && 'Table'[Create Time]>=TIME(8,00,00)))

return
DIVIDE(_a,_b,0)

 

Outside Working Hours

average (17:01-7:59) = var _c=CALCULATE(SUM('Table'[First Response (Minute)]),FILTER('Table', 'Table'[Create Time]>=TIME(17,01,00) || 'Table'[Create Time]<=TIME(7,59,00)))
var _d=COUNTROWS(FILTER('Table','Table'[Create Time]>=TIME(17,01,00) || 'Table'[Create Time]<=TIME(7,59,00)))
return 
DIVIDE(_c,_d,0)

 

It was working fine but i changed my data source and suddenly they are returning "blanks" and i can't figure out what went wrong!!

 

I have attached a sample data file 

Column E - Create Time

Column M - First Response Time (Mins)

 

I want to create a measure that finds the average First Response Time (Mins) that occured during working hours (08:00-17:00) and those that occured outside working hours (17:01-7:59)

 

Sample Data Link 

 

Thank you,

 

2 Replies

  • anusornkhurana , Avg at what level

     

    Example at hour level

     

    Sumx( Values('Table'[Hour]),
    CALCULATE(SUM('Table'[First Response (Minute)]),FILTER('Table', 'Table'[Create Time]<=TIME(17,00,00) && 'Table'[Create Time]>=TIME(8,00,00)))
    )

    • anusornkhurana's avatar
      anusornkhurana
      Regular Visitor

      At the hour level

      average of first response (mins) that occured to all tasks created [Create Time] between 8.00-17.00

       

      I tried the measure you gave me it didn't work. It is still returning Blank value 😞

       

      Thank you!