How do I calculate average Response Time only between work hours 8AM-4PM

[nyit]

I hope some one here can help me out, preferably in layman terms (i'm still new to the power bi).

I've been asked by my boss to set up a little view of our Spiceworks helpdesk system (ticket status, work time, customer, response time etc.) and it went great until he wanted an "average response time" but only during normal work hours.

example of my columns

Everything runs with a ODBC connection to an SQLite DB, Spiceworks automatically creates a column [created_at] as datetime of the creation time of the tickets(day/month/year HH:mm:ss). from here i've duplicated the column to [created_at_hour], extracted the HH:mm:ss and transformed it to time - so far so good.

now i would think that i need some kind of "if" statement, along the lines of 

if [created_at_hour] > #time(8,0,0) and [created_at_hour] < #time(16,0,0) then [created_at_hour] else null 

but it does not work, and i have no idea of why, but i do know that i properly mixed up M, statements and DAX - Sorry!

Wish full thinking:

I would like to end up with one column that only have time data, if [created_at_hour] is equals or bigger then 08:00:00 and 16:00:00 else "blank"

[v-piga-msft]

Hi @nyit,

You could create the calculated column with the formula below.

Column =
IF (
    AND (
        HOUR ( 'table'[Created_at _hour] ) >= 8,
        HOUR ( 'table'[Created_at _hour] ) < 16
    ),
    'table'[Created_at _hour],
    BLANK ()
)

Then you will get your expected output.

For reference, you could have a look at my test pbix file attached below.

Best Regards,

Cherry

[nyit]

Hi @v-piga-msft,

thank you for your reply

but apparently i'm not doing it right, so i don't know if it works.

perhaps you could help me out once more?

[v-piga-msft · Accepted solution]

Hi @nyit,

It seems that you have made a mistake on the comma after '8'.

Please change the Comma in red to ';', this formula should work.

In addition, when you have written a dax formula, you could copy your dax formula to this dax tool which could help you check if your formula has syntax error and format it.

Hope this can help you!

Best Regards,

Cherry

[nyit]

Hi @v-piga-msft,

Sorry for the late answer.

Thank you, that worked and the dax formatter is really great to check for errors.

[v-piga-msft]

Hi @nyit,

That's great! My pleasure.

Best Regards,

Cherry