Forum Discussion
Creating a Time Filter
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.
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.