Forum Discussion
Creating a Time Filter
Hi,
I am quite new to PowerBI and i am currently working on a report related to First Response Time (ITSM-SLA)
I have data according to the table below. What i want to do it. I want to filter What is the average first response (minutes) for all tickets created between 8:00 - 17:00 (Working Hours) and what is the average first response (minutes) for all other tickets (17:01 - 07:59(next day)).
All help is appreciated!!
Thank you
| Create Date | Create Time | First Response (Time) | First Response (Minute) |
| 2023-01-03 | 09:28 | 11:06 | 98 |
| 2023-01-03 | 09:36 | 16:17 | 401 |
| 2023-01-04 | 13:37 | 09:24 | 29987 |
| 2023-01-05 | 10:34 | 10:09 | 20135 |
| 2023-01-06 | 08:06 | 10:20 | 134 |
| 2023-01-06 | 08:09 | 08:33 | 24 |
| 2023-01-06 | 08:17 | 09:01 | 44 |
| 2023-01-06 | 08:23 | 09:57 | 94 |
Hi anusornkhurana ,
If you want your results to be affected by slicers, consider creating measures.
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)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)What is your logic for [During Work Hours] and [Outside Working Hours]? If you want to sum the First Response (Minute) during working hours, you can try:
during working hours = CALCULATE(SUM('Table'[First Response (Minute)]),FILTER('Table', 'Table'[Create Time]<=TIME(17,00,00) && 'Table'[Create Time]>=TIME(8,00,00)))outside woring hours = CALCULATE(SUM('Table'[First Response (Minute)]),FILTER('Table', 'Table'[Create Time]>=TIME(17,01,00) || 'Table'[Create Time]<=TIME(7,59,00)))Then the result is as follows.
If this doesn't meet your needs, my suggestion is to mark the answer to the current post as a solution, and then open a new post to describe the detailed problem, thank you for your time.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi anusornkhurana ，
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create two calculated columns.
Column = 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)Column 2 = 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)(3)We can create two tables.
Table 2 = DISTINCT(SELECTCOLUMNS('Table',"average",'Table'[Column],"woring hours","8:00-17:00"))Table 3 = DISTINCT(SELECTCOLUMNS('Table',"average",'Table'[Column 2],"woring hours","17:01-7:59"))(4) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It works! But can i ask you a little more. If i want to go further another step.
At the moment it isn't dynamic.
The "501.71 Average First Response Time (Mins)" was created using the "First Response Time (Mins)" column from the Table in my previous post.
When i change the year, month, or day the value changes accordingly. But the "During Work Hours" & Outside Working Hours" remains the same. Is it possible for it to change according to the Year, Month or Day filter that is applied?.
Thank you!
Hi anusornkhurana ,
If you want your results to be affected by slicers, consider creating measures.
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)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)What is your logic for [During Work Hours] and [Outside Working Hours]? If you want to sum the First Response (Minute) during working hours, you can try:
during working hours = CALCULATE(SUM('Table'[First Response (Minute)]),FILTER('Table', 'Table'[Create Time]<=TIME(17,00,00) && 'Table'[Create Time]>=TIME(8,00,00)))outside woring hours = CALCULATE(SUM('Table'[First Response (Minute)]),FILTER('Table', 'Table'[Create Time]>=TIME(17,01,00) || 'Table'[Create Time]<=TIME(7,59,00)))Then the result is as follows.
If this doesn't meet your needs, my suggestion is to mark the answer to the current post as a solution, and then open a new post to describe the detailed problem, thank you for your time.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Works Superb!!
Thanks alot!
