Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
anusornkhurana
Regular Visitor

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 DateCreate TimeFirst Response (Time)First Response (Minute)
2023-01-0309:2811:0698
2023-01-0309:3616:17401
2023-01-04 13:3709:2429987
2023-01-0510:3410:0920135
2023-01-0608:0610:20134
2023-01-0608:0908:3324
2023-01-0608:1709:0144
2023-01-0608:2309:5794
1 ACCEPTED SOLUTION

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.

vtangjiemsft_0-1675835527766.png

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. 

View solution in original post

4 REPLIES 4
anusornkhurana
Regular Visitor

Works Superb!!

 

Thanks alot!

v-tangjie-msft
Community Support
Community Support

Hi @anusornkhurana ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

vtangjiemsft_0-1675821988013.png

(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.

vtangjiemsft_1-1675822098290.png

 

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!

 

2023-02-08_12-00-18.png

 

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.

vtangjiemsft_0-1675835527766.png

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. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.