Elapsed Working Hours (different per country) excluding weekends

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)

Any advice would be really appreciated.

Thank you so much.

Hi @Jetuke_ES

Calculated column - When you want to refer same row values don’t use aggregations

How you want to show non working hours like below.

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?


For example, here i would expect to have around 11 hours, not 23.

Any advice would be appreciated.

Rajesh, could you advise how i could account for the elapsed time between Steps (execution order column) per case? Thank you so so much!

@Jetuke_ES

What is the logic to get 11 hours for below record

For example, here i would expect to have around 11 hours, not 23.

Please provide expected output for few records

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.

So the G2 = is hours but excluding Weekend and the non working hours (as per E2 and F2). And H3 = C3-B2. For the same case.

Hope i am a bit clearer, erika

@Jetuke_ES

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
Yes, you are totally right! It is 2:17:00.

@Jetuke_ES

I’m working on your request, but I’ve couple of questions.

Both starttime and EndTime same day non working hours. What is the expected output ?

If Starttime in working hours and endtime in non working hours.
EX. StartTime 10/16 4:00 PM EndTime 10/16 9:00 PM

Could you please provide all the scenarios and expected output

@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) :slight_smile:
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". :slight_smile:

Hi @Rajesh,
Could i maybe provide more information?

thank you so much for all your help,
erika

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 </>.

image

  • 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 also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

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 :smiley: 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

Elapsed work hours_TEST.pbix (68.0 KB)

@Jetuke_ES

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

Elapsed Work Hours Per Step Per Case Sample.pbix (75.2 KB)

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.

Any help if possible would be appreciated.

thank you.

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.
AU_hours

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!

Well, this post has no complete solution, sadly. But if you need to tick that box, I understand.