Need Help on DAX Formula to calculate the elapsed time - Urgent

[PrakashSoorya]

Dear BI Community,

I need help to create a DAX formula for Calculating the Elapsed time between 2 works. Below is the case example.

Column A contains Start time 02/01/2023 11:30:24 & Column B contains End time 19/01/2023 02:15:30, Now we need to calculate the elapsed time in HOURS with below considerations,

I tried few iterations, But i couldn't make it. 

Pls help me on this.

1. The result should not capture the Weekends ( Sat & Sun ) between the start and end given in A & B columns. But while doing this we might face some issues like in some cases, The Start may be Friday evng and End may be in Mid night 2am of saturday, So the date changes and will be considered as saturday and this will be ignored, But in ideal this should also be taken in account. So this should also be kept in mind before framing the formula.

2. We are operating in 2 shifts ( A shift - 7am to 4.45pm & B Shift - 4.46pm to 2.20am ) considering 19.2 hours per day and not 24 hrs per day. Once 2 shifts are completed i.e., from 2.21am to 6.59am remains non-operational hours, so the result should not to include these time in each cases.

3. The result should not capture the special holidays ( Regional Festival Holidays ) for example in given case example, the festival holidays was 16th,17th & 18th Jan'2023. so these days should not be captured in the results. and these dates are dynamic, our next set of special holidays will come on march, So the formula should have provison to automatically exclude these special holidays when once feeded in intially ( Like we do in Networkdays.Intl Formula in Excel )

[Greg_Deckler]

@PrakashSoorya You have a NETWORKDAYS function in DAX as well. There is also an old school method of doing that as well. Also, I put together this: Net Work Duration (Working Hours) - Microsoft Power BI Community

But, you will have to have some better business rules in place rather than "Sometimes you count Saturday and sometimes you don't".

[PrakashSoorya]

Dear @Greg_Deckler ,

Thank you for the response. 

By deploying the below formula, iam getting an negative value and that too for all different data's. 

May be iam making a mistake some where ?

CAn you pls help me on troubleshooting. Attaching a snap for reference ( TPT-2 Column is the just the answer brought in from excel just to compare )

Thank you.

Awaiting your reply.

[Greg_Deckler]

@PrakashSoorya Not sure, here is an implementation in a PBIX for your dates and times given. It comes out positive. I put it in hours. PBIX is attached below signature. Oh, wait! I see the issue, you implemented it as a column. Drop the MAX from the first 2 lines (3 and 4). Otherwise you are getting the max of the entire table instead. The way it is written is for a measure.

[PrakashSoorya]

Dear @Greg_Deckler ,

I tried to implement your recent input and found the below result ( Attachment )

Actually removing MAX made to proper application of formula so that i get different answers based upon the different times. But unfortunately answers are not correct. 

Can you pls help on this.

Thanks in Advance.

[Greg_Deckler]

@PrakashSoorya You'll have to be more specific in terms of what is wrong with the calculations. For example, taking your first row of data. There are 24 days between the two dates, 6 of which are weekends and thus 18. Working day is 10.5 hours or 189 hours total. If we subtract the seven hours accounting for the start and stop of the work day then you end up with 182 hours, roughly. Is that the answer you are looking for from the first row?

[PrakashSoorya]

Dear @Greg_Deckler ,

I'm putting the requirement in simple text, 

There are 2 times ( Start date + Time AND End date + Time ) , We need to subtract these and get the net elapsed time in hours, Provided the results must Exclude Weekends, Exclude the non-working hours ( Per day our Company works 19.2 hrs only, A shift 7am to 4.45pm and B Shift is 4.46pm to 2.20am, Rest of the 4 hours and 40 minutes is non-operative. ) and Special holidays if any. 

Also for even better clarity, I have attached a file where one case example, the first row from the data table is been explained. Pls go thru and i believe the requirement is clear now and u will able to help me. 

Also one last thing, When i used formula from the last reply of yours , Answers are started coming, But somehow they are not accurate and few line items are wrong ( Refer attachement of my previous reply. Some line items are negative and some are in zero's like 0.232375, which is not possible.

Somehow my feel now is we are very close and only needs few correction. 

Awaiting to get the best solution.

Thank you.

[Greg_Deckler]

@PrakashSoorya I think this is accurate. Take a look at PBIX attached. I believe the difference is that 20 minutes is .33333 of an hour, not .2 of an hour. 

mNetWorkDuration = 
// Get the start and end dates
VAR __dateStart = MAX('Table'[Column1])
VAR __dateEnd = MAX('Table'[Column2])
// Calculate the Net Work Days between the start and end dates
VAR __NetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR(__dateStart,__dateEnd),"WeekDay",WEEKDAY([Date],2)),[WeekDay]<6),[Date])
// Set this to the start of the work day (7:30 AM)
VAR __startTime = TIME(7,00,0)
VAR __startTime2 = TIME(2,20,0)
// Set this variable to the end of the work day (6:00 PM)
VAR __endTime = TIME(23,59,59)
// Calculate the duration of a full day, in this case in minutes
VAR __carryOverMinutes = DATEDIFF(TIME(0,0,0),__startTime2, MINUTE)
VAR __fullDayMinutes = DATEDIFF(__startTime,__endTime,MINUTE) + 1 + __carryOverMinutes
// Calculate teh number of full days, this accounts for the possibility that tickets start and end on the same day
VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
// Calculate the total duration of all full days.
VAR __fullDaysDuration = __fullDays * __fullDayMinutes 
// Calculate the start time of the current record
VAR __startDayTime = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
// Caclulate the duration of time for the first day
VAR __startDayDuration = DATEDIFF(__startDayTime,__endTime,MINUTE) + 1 + __carryOverMinutes
// Calculate the end time of the current record
VAR __endDayTime = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
// Calculate the duration of time for the last day
VAR __endDayDuration = DATEDIFF(__startTime,__endDayTime,MINUTE) + 1 + __carryOverMinutes
// The total duration is the duration of all full days plus the durations of time for the first and last days
RETURN  
    IF(__NetWorkDays=1,DATEDIFF(__dateStart,__dateEnd,MINUTE)/60,__fullDaysDuration + __startDayDuration + __endDayDuration)/60

[PrakashSoorya]

Dear @Greg_Deckler , 

Firstly iam sorry for the delayed response. I was Out of Office and stationed in a remote rural area.

Getting into our topic, 

I just deployed your latest formula into my file and its showing an error.

I have attached the error snap and also the file in here. 

Pls have a look and help fo the solution.

Thanks in Advance

[Greg_Deckler]

@PrakashSoorya Looks like line 6 maybe? Looks like you need to swap your two parameters.

[PrakashSoorya]

Hello @Greg_Deckler ,

I tried swapping the parameters of Line 6, As you predicted error in DAX gone. 

But the desired results are coming in negative and in decimals instead of hours ????

pls have a view.

Also pls tell me how do we attach the file in this chat box, in this replies as u do send your BI files, Iam not able to see any attach option to share my file with you in this chat box which will make us the job easier.

Thank you. 

[Greg_Deckler]

@PrakashSoorya Swap your CALENDAR parameters back and edit your dateStart and dateEnd VAR's to swap those so that dateStart is Rollout Time and dateStart is PBuyoff Out. You'll have to upload your PBIX to OneDrive or Box or something and share it.

[PrakashSoorya]

Dear @Greg_Deckler , 

Even after swapping the DateStart and DateEnd VAR as you advised, Still Issue persists and answer is not fetched. Pls have a view on the snap.

And just for clarity, iam mentioning again, Our DateStart is Pbuy off out time and DateEnd is PQC OK Time.

And even after i put my file in One Drive, Iam unable to share (may be due to Business Network Firewall) i can share thru Gmail. Can i have your gmail id pls. ??

I believe with the file you will help me in better and accurate  way.

Thank you.

[Greg_Deckler]

@PrakashSoorya Now that you switched your VAR's, you need to swap your CALENDAR parameters back on line 6.

[PrakashSoorya]

Dear @Greg_Deckler ,

I have swapped the Line 6 parameters as advised, Still An error. 😞 

Now its showing, The start date or end date in calendar function can not be Blank value. Pls refer snap. ( Earlier it showed, Start Date in calendar function can not be less than End value )

[PrakashSoorya]

Dear @Greg_Deckler ,

Awaiting your support.

Thanks in Advance.

[PrakashSoorya]

Dear @Greg_Deckler 

Awaiting your support.

Thanks in Advance.