Calculate difference between 2 dates

[spaiva]

-2

I have been asked to make a Power BI report where it shows some details about organization service desk. I've made it easily but, there is one thing there I couldn't figure out: they asked me to show the average time that every support analyst take to resolve the tickets. The problem is: I have to calculate the avg time only between 8:00 AM and 6:00 PM in every day. it means that a day have only 10 hours. I tried to do this in SQL (MySQL) but nothing came out.

I tried:

CASE WHEN EXTRACT(HOUR FROM tick.CREATED) >= 8 AND
EXTRACT(HOUR FROM tick.TIME_CLOSED) <= 18 AND
weekday(tick.CREATED) not in (6,7) THEN
TIMESTAMPDIFF(HOUR,tick.CREATED,tick.TIME_CLOSED) END

But I don't know a way to skip the hours out of this range and continue counting the hours in the next day.

Is there a function or something in Dax that can help me with that?

Thanks a lot.

[mwegener]

Hi @spaiva ,

check this out.

https://docs.microsoft.com/en-us/dax/datediff-function-dax

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.