Calculate Business Hours using DAX (Working Hours in Power BI & Power Pivot - Excel)

preview_player
Показать описание
Short and easy! You'll love this solution!

Workday = If(Weekday(Calendar_Table[Date]) = 7 || Weekday(Calendar_Table[Date]) = 1, 0,1)

Start =
var workDayStart = time(8,0,0)
var eachdate = DATEVALUE(Calendar_Table[Date])
return eachdate + workDayStart

End =
var workEndStart = time(17,0,0)
var eachdate = DATEVALUE(Calendar_Table[Date])
return eachdate + workEndStart

Resolution Time (Hrs) =
VAR _Start = 'Request Data'[Created At]
Var _End = 'Request Data'[Resolved At]
Return SUMX(
CALCULATETABLE(
Calendar_Table,
DATESBETWEEN(Calendar_Table[Date],_Start,_End),
Calendar_Table[Workday] = 1
),
MAX(MIN(Calendar_Table[End],_End) - MAX(Calendar_Table[Start],_Start),0) * 24
)
Рекомендации по теме
Комментарии
Автор

Workday = = 7 || Weekday(Calendar_Table[Date]) = 1, 0, 1)

Start =
var workDayStart = time(8, 0, 0)
var eachdate =
return eachdate + workDayStart

End =
var workEndStart = time(17, 0, 0)
var eachdate =
return eachdate + workEndStart


Resolution Time (Hrs) =
VAR _Start = 'Request Data'[Created At]
Var _End = 'Request Data'[Resolved At]
Return SUMX(
CALCULATETABLE(
Calendar_Table,
DATESBETWEEN(Calendar_Table[Date], _Start, _End),
Calendar_Table[Workday] = 1
),
MAX(MIN(Calendar_Table[End], _End) - MAX(Calendar_Table[Start], _Start), 0) * 24
)

PowerGI
Автор

I tried to find this everywhere and you gave the best explanation in 5 minutes. Congratulations and thank you so much!

caiordgs
Автор

Really handy description and explanation. I had to modify it to suit my needs but this was a great foundation (calculate ticket handling in hours per support region). Thank you so much!

pinecones-dk
Автор

Very Helpful and Simple Your 5 mins video saved me 2 full days of research and scratching my brains :)

sreenivas
Автор

Hi, this has been the best answer and helped me with my issue. Thank you!

will
Автор

It works thanks for your help :) additionally wanted to know if we can get the holidays removed

mughilmohanan
Автор

Great video, what about adding x business hours to datetime to work out end datetime of SLA?

johnnyp
Автор

Not calculating correctly when the end day is different from the start.

jeanobelo
Автор

Thank you so much! It really helped in my project.

Learn-M
Автор

Where to add networkdays formula for SLA

srinidhistiwari
Автор

If working times are open 24 hours from 8am Monday to 4pm Friday, how would I change this formula?

DavidEly-dj
Автор

Great solution really. I would like to have in the return statement, the due date and time (10 hours in addition), instead of nb of resolution time in hours. It is possible?

Radja
Автор

Can you advise how I would do it if I had a different end time on Friday

I.e.
Monday to Thursday is 8-5.30
Friday is 8-5.00

Sat and Sun is not open

I have got it to work but everyday finishes at 5.30 and need Friday to be 5.00

Thank you in advance

realadamberry
Автор

How would the if statement look if for example Monday- friday 07:00 - 17:00 but on saterday it will be 07:00-13:00?

hanneskruger
Автор

Does anyone know how I can modify this for multiple countries that have different start and end and holidays? I tried creating the records into the calendar date and a many to many relationship for the country between dim and date table but it doesn’t work

alpeshpatel
Автор

Great Video. I have searched everywhere this one. I found it here. Very Clear. By the way could you please tell me how to calculate one additional column to set the Due Date and Time

Radja
Автор

Hi this a excellent video,
How can I add holidays?

TheAlphadog
Автор

hola, gracias por tu video esta genial, me puedes ayudar diciéndome por gavor como vuelvo los festivos en 0 gracias

lizethchaparro
Автор

Hello, thank you for your video, it's great, can you help me by telling me please how I return on holidays in 0, thank you

lizethchaparro
Автор

not calculating correctly when the end day is different from the start.

KnightFM-Ops