Forum Discussion
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
- HashamNiaz
Solution Sage
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,