Forum Discussion

lisamb1's avatar
lisamb1
Regular Visitor
5 years ago
Solved

How to Get Time Difference Between Two Date/TIme Columns by Creating a New Column

Hello Community,

 

I have a table that has a "Created" date/time field and a "LastUpdated" date/time field. I would like to create a new column that will display the amunt of time difference between the 2 columns. When the new column is created using the following DAX the column displays the same result for every row and the values are not unique.

 

CustomDurationAverage =

       VAR total_second = 

       AVERAGEX(

                'tablename'

                DATEDIFF('tablename' [Created], 'tablename' [LastUpdated], SECOND)

)

 

      VAR NEWDay = INT(total_second/(24*60*60))

      VAR NEWHours = MOD(INT(total_second/(60*60)),24)

      VAR NEWMin = MOD(INT(total_second/60),60)

      VAR NEWSec = MOD(total_second,60)

 

      RETURN NEWDay &" Day "& NEWHours &" Hour "& NEWMin &" Minute "& NewSec & " Second"

 

This is what I am receiving for all of the results in the new CustomDurationAverage column

0 Day 1 hour 32 Minute 58.9946632653064 Second

 

Is there another way of creating a new column with the results or what am I missing in the DAX code above? Is there a way that I can display the results in the new CustomDurationAverage column as 00:00:00:00 instead of as a text field with each vaule broken out?

 

Thank you all for your help!

 

 

  • Hi !

    you can use the following DAX to create a new column;

     

    CustomDurationAverage = 
        VAR total_second = 
            DATEDIFF('tablename'[Created], 'tablename'[LastUpdated], SECOND)
        VAR NEWDay = INT(total_second/(24*60*60))
        VAR NEWHours = MOD(INT(total_second/(60*60)),24)
        VAR NEWMin = MOD(INT(total_second/60),60)
        VAR NEWSec = MOD(total_second,60)
    RETURN 
        NEWDay &" Day "& NEWHours &" Hour "& NEWMin &" Minute "& NewSec & " Second"

     

    Regards,

3 Replies

  • Hi !

    you can use the following DAX to create a new column;

     

    CustomDurationAverage = 
        VAR total_second = 
            DATEDIFF('tablename'[Created], 'tablename'[LastUpdated], SECOND)
        VAR NEWDay = INT(total_second/(24*60*60))
        VAR NEWHours = MOD(INT(total_second/(60*60)),24)
        VAR NEWMin = MOD(INT(total_second/60),60)
        VAR NEWSec = MOD(total_second,60)
    RETURN 
        NEWDay &" Day "& NEWHours &" Hour "& NEWMin &" Minute "& NewSec & " Second"

     

    Regards,

    • lisamb1's avatar
      lisamb1
      Regular Visitor

      HashamNiaz, thank you this resolved the issue! Is there a way to dispaly the CustomDurationAverage as 00:00:00:00 instead of as 0 Day 0 Hour 59 Minute 40 Second?

       

      Thank you again for all of your assistance!