Dates/Time/Duration Value Display

[shado26]

Hi All,

hope all are fine 

i need your assist here to see time in Days hours minutes and seconds 

i use the below formula for time Diff but it show only days is there any way to see all  and as my frist action are Quote Closed i don't want formula to calculate the time here 

TIme Diff = if(Merge1[ActionName]="Quote Closed",0,DATEDIFF(Merge1[Pervious action],Merge1[Current A
tion],MINUTE)/1440)

also i tried the below formula for Day hours minute & Seconds its how negative time which i want to remove as well 

Time = 
var allseconds = DATEDIFF(Merge1[Pervious action],Merge1[Current Action],SECOND)
var days = int(allseconds/24/60/60) 
var hours = mod(int(allseconds/60/60),24)
 var minutes = mod(int(allseconds/60),60)
 var seconds = mod(allseconds,60) 

return days &" days " & hours & " hours " & minutes & " minutes " & seconds & " seconds" & IF(Merge1[ActionName]="Quote Closed",0)

at the end i need to see each step time and to get sum all of time 

appreciate if you can help me on this 

BR 

Shadi

[rajulshah]

Hello @shado26,

You can use the measure to sum all the time differences and time difference will be calculated as calculated column.

Regards.

[shado26]

Hello @rajulshah

thank for reply but i need to see all Day hours minute & Seconds

[rajulshah]

Can you replicate the result data you want to see?

I cannot understand the requirement properly.

[shado26]

hello @rajulshah

1- on time Diff formula i want to Day hours minute & Seconds if it can be done 

2- on Time formula i need to rmeove negative time and sum all the time 

[rajulshah]

Do you want data shown as below? Please confirm.

[shado26]

YEs but i correct Value as per my Ex it should be 

8 day 00 Hours 49 minutes 31 seconds 

[shado26]

good morning guys 

appreciate if you can help me here 

[rajulshah]

Hello @shado26,

You can try here this:

Booking Creation to Appointment =
VAR TotalSeconds=SUM(table[differenceintime])
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 "

Hope this works.

[shado26]

HI @rajulshah

thank you for your reply  but still not work its show the same duration time in all lines 

[v-huizhn-msft]

Hi @shado26,

Could you please share your .pbix file for further analysing? So that we can provide dedicted solution.

Best Regards,
Angelia

[shado26]

HI @v-huizhn-msft

kindly find attach file for your help and assist 

Test File

[rajulshah]

Hello @shado26,

Is this what you want?

See below:

Please confirm. 

[shado26]

Yes thats is can you share the PBIX 

[rajulshah · Accepted solution]

Hello @shado26,

I cannot share the pbix file, but these are steps that I folowed.

1. Make a calculated column:

Datediff = DATEDIFF(Merge1[Pervious Time],Merge1[Current Time],SECOND)

2. Make a calculated measure:

Measure =
VAR TotalSeconds=SUM(Merge1[Datediff])
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 "

Please let me know if this worked.

[shado26]

Hi @rajulshah

can we add in this formula to exclude the weekends and working time 

please note that our weekend Friday and Saturday 

& working hours from 9 AM to 5 PM

[shado26]

thank you @rajulshah work perfectly