Hi experts,
I’m seeking your help, please, as I am a little stuck with finding a solution.
In my scenario I have a Case ID, Start date/time and End Date/time per steps to finalize the Case. I need to calculate the elapsed working hours (or minutes) for each Step and also between steps. To add to that Working hours are different dependent on the country (3 variables). I can calculate Workdays elapsed, but cant find a way for hours. I looked at this solution: Calculate the work hours between 2 dates( datetime) excluding weekends - 9 am - 6pm Mon-Fri and couple other solutions online.
But don’t seem to find a way to exclude non working hours by country, and to exclude weekends.
I have attached my working pbix, removed sensitive data, but left my trial dax’es in there. Elapsed Work Hours Per Step Per Case Sample.pbix (72.3 KB)
Thank you so much Rajesh! Great tip. But I don’t think it takes into account only the “working hours” into account, does it? It just calculates total the hours/minutes elapsed? Do you know how i could exclude the non working hours from that calculation?
Thank you for your help Rajesh. I really appreciate it.
Here is what i would need to have at the end, if possible. It is possible in excel, cant find a way with Dax yet. I’m sure there must be away somehow.
First record elapsed hours should be 2:17:00 right ? Not 1:17:00
Start Date 16/10 19:00 non working hour next working day is 19/10 8:00 AM and End time 10:17
@Rajesh Thank you so much for the help!
If both start and end date/times are in non working hours - would expect 0 hours. The reason some of those are still there is because some steps are automatic, dependent on the categorization (some context)
For the second scenario that you asked: EX. StartTime 10/16 4:00 PM EndTime 10/16 9:00 PM, it would be, 2 hours. From 4pm until 6pm, as its on Friday. If it was weekend, would just expect 0.
I understand, some are self conflicting but that’s how “we have it” at the moment".
Hi @Jetuke_ES, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
I understand Rajesh couldn’t find a solution.
Here is to where I’ve got to so far. Could someone please help me find the issue in dax? @BrianJ you seem like an expert in this area is there anything that jumps out to you in my dax? Any help would be really appreciated. Thank you in advance… Preformatted textBus_Hours =
IF(
[SM]=[EM],
0,
VAR BusHoursStart =
CALCULATE(
SELECTEDVALUE ( BusHours[Time]),
BusHours[Business Hours] = “Start”,
BusHours[Country] = “FR”
|| BusHours[Country] = “AU”
)
VAR BusHoursEnd =
CALCULATE(
SELECTEDVALUE(BusHours[Time]),
BusHours[Business Hours] = “End”,
BusHours[Country] = “FR”
|| BusHours[Country] = “AU”
)
VAR BusHoursPerDay =
VALUE (BusHoursEnd - BusHoursStart) * 24
VAR ThisStartTime = ElapsedHoursReport[StartTime]
VAR ThisEndTime = ElapsedHoursReport[EndTime]
VAR StartDate = ElapsedHoursReport[Start date]
VAR EndDate = ElapsedHoursReport[End date]
VAR FirstDayElapsedTime =
SWITCH(
TRUE(),
RELATED( Dates[Working Days]) = 0, 0,
ThisStartTime >= BusHoursEnd, 0,
ThisStartTime <= BusHoursStart, BusHoursPerDay,
StartDate = EndDate
&& ThisEndTime < BusHoursEnd,
ROUND(( ThisEndTime - ThisStartTime) * 24, 3 ),
ROUND ((BusHoursEnd -ThisStartTime) * 24, 3))
VAR LastDayElapsedTime =
SWITCH(
TRUE(),
LOOKUPVALUE( Dates[Working Days], Dates[Date], EndDate) = 0, 0,
ThisEndTime <= BusHoursStart, 0,
ThisEndTime>= BusHoursEnd, BusHoursPerDay,
StartDate = EndDate, 0,
ROUND ( ( ThisEndTime - BusHoursStart ) * 24, 3) )
VAR FullWorkDays =
CALCULATE (
SUM ( Dates[Working Days]),
DATESBETWEEN(Dates[Date], StartDate + 1, EndDate -1) )
VAR TotalHours = FirstDayElapsedTime + FullWorkDays * BusHoursPerDay + LastDayElapsedTime
Return
TotalHours
) Preformatted text
I didn’t get much to work on this. here is the half solution…
there is a problem if it crosses 24 Hrs… Its not showing complete hours… Please check and let me know how you want to see if it crosses 24 hrs.
Is it ok show like below
LIKE 1 day 10 hours… 2 days 16 hours
Hi @Jetuke_ES, did the response provided by @Rajesh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Very sadly, it doesn’t really help to resolve my problem to be honest. : ( My attached (almost) solution got closer to resolving it, as per attached and post above. Thank you @Rajesh for trying.
Do you know how I can convert from a calculated column decimal data type into hours. for example from 4.78 to 4:47.
Could someone please have a look at my dax to help me find why my AU country keeps coming back with 14 hours, when it is calculating correctly other rows? Please, any help would be appreciated.
I have also attached the pbix. The dax is in the post above, in my post from Dec 16th. Elapsed work hours_TEST.pbix (72.4 KB)
Hello @Jetuke_ES, due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread. When starting a new post, I suggest linking this thread, provide as much context to a question as possible and include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details. Thanks!
is there anything that jumps out to you in my dax? Any help would be really appreciated. Thank you in advance…
