Forum Discussion

shado26's avatar
shado26
Icon for Helper III rankHelper III
8 years ago
Solved

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 onl...
Need Help
Tips and Tricks
  • rajulshah's avatar
    rajulshah
    8 years ago

    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.

rajulshah's avatar
rajulshah
Icon for Resident Rockstar rankResident Rockstar
8 years ago

Hello shado26,

 

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

 

Regards.

shado26's avatar
shado26
Icon for Helper III rankHelper III
8 years ago

Hello rajulshah

 

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

  • rajulshah's avatar
    rajulshah
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Can you replicate the result data you want to see?

     

    I cannot understand the requirement properly.

     

     

    • shado26's avatar
      shado26
      Icon for Helper III rankHelper III
      8 years ago

      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's avatar
        rajulshah
        Icon for Resident Rockstar rankResident Rockstar
        8 years ago

        Do you want data shown as below? Please confirm.