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

[lisamb1]

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!

[HashamNiaz · Accepted solution]

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]

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!

[lisamb1]

I figured it out. Thanks!