Forum Discussion

Kratchie's avatar
Kratchie
Frequent Visitor
3 years ago
Solved

Calculate duration from fixed date/time

Hi,   I've a column with fixed time which begins from 2000-01-01 00:00:00 If we find 2000-01-02 03:10:10 in that column it means 27h, 10 min, 10 sec in duration. How can I calculate and get it as ...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Kratchie 

    not sure about your ultimate goal, try this:

    Column =
    VAR _d = TRUNC( [Duration]-DATE(2000,1,1))
    VAR _hms = FORMAT((([Duration]-DATE(2000,1,1))-_d),"HH:MM:SS")
    RETURN _d &" Days " &_hms
     
    I tried and it worked like this:
ppm1's avatar
ppm1
Icon for Solution Sage rankSolution Sage
3 years ago

I assume you are later going to aggregate your durations in a measure, so please see this article for a good way to handle durations as decimals (in days) so you can easily do math and use them in visuals, but then display them as hh:mm, etc. when needed.

Calculate and Format Durations in DAX – Hoosier BI

Pat

  • Kratchie's avatar
    Kratchie
    Frequent Visitor
    3 years ago

    Hi ppm1,

     

    This I have done before without any problem when it's two columns with both start/end date+time (split it into 4 columns (start&date, end&date, start&time, end&time) but now I need to get duration from fixed timestamps as I wrote above which isnt that easy. I have googled for different solutions but couldn't find any.