Exclude Weekend and working hours

[shado26]

Dear Team

appreciate your assist to upgrade below formula to exclude Weekend and working hours attached test file 

Test PBIX File

i have been used the below formula 

Time Diff2 = VAR PreviousTime=TOPN(1,Filter(Table1,Table1[Orderno]=earlier(Table1[Orderno])&&Table1[LogDatetime]<earlier(Table1[LogDatetime])),[LogDatetime],desc)
RETURN
DATEDIFF(MINX(PreviousTime,Table1[LogDatetime]),Table1[LogDatetime],SECOND)

Duration = 
VAR TotalSeconds=SUM(Table1[Time Diff2])
VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hours = TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)
VAR Secs = CEILING(MOD(TotalSeconds,60),1)
return IF(DAYS=0,"",IF(DAYS>1,DAYS&" days "))&IF(Hours<10,"0"&Hours,Hours)&" hours "&IF(Mins<10,"0"&Mins,Mins)&" minutes "&IF(Secs<10,"0"&Secs,Secs)&" seconds "

[v-juanli-msft]

Hi @shado26

As I understand, when excluding Weekend and working hours, the second row of "Time Diff2" would change to be 0 instead of 38.

Since excluding Weekend and working hours means excluding any time in Saturday and Sunday, along with time period of 8:00 -18:00 of each day from Monday to Friday.

Is my understanding right?

Best Regards

Maggie

[shado26]

Hi @v-juanli-msft

actually our Weekends are Saturday and Friday , working hours between 9 AM till 6 PM 

the above formula  are measure the time difference  between actions  but weekend and working hours are not exclude 

can we do something about this ??? 

[v-juanli-msft]

Hi @shado26

Yes, it can be achieved, but it will take sometime for me to give proper answer in your scenario.

You could refer to this link which is similar to your case to make a try on your site.

https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/m-p/374255/highlight/true

I'll come back as soon as I complete it.

Best Regards

Maggie

[shado26]

@v-juanli-msft thank you for your time , i will wait for your feedback 

can you share the link 

[shado26]

Hi @v-juanli-msft

waiting for your kind feedback 

[shado26]

hi @v-juanli-msft

any update ??

[shado26]

HI @v-juanli-msft

any update ??

[v-juanli-msft]

Hi @shado26

I'm very sorry that I can't figure out this problem.

I am a junior engineer and new one of this technical support team. What I have done in your pbix file can't completely meet your requirement.

Maybe you could copy this thread and open another thread, thus others may have chance to take charge of your problem.

Best Regards

Maggie

[shado26]

hey guys appreciate  your help here 

[shado26]

Dear Team need your urgnet help