Forum Discussion
Dates/Time/Duration Value Display
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
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.
