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:
ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
3 years ago

Hi,

Here is one way to do this:

Measure = var _mindate =CALCULATE(MIN('Table (2)'[Column1]),ALL('Table (2)'[Column1]))
var _h =  DATEDIFF(_mindate,MAX('Table (2)'[Column1]),HOUR)
var _m = MINUTE(MAX('Table (2)'[Column1]))
var _s = SECOND(MAX('Table (2)'[Column1]))
return

_h & "h " & _m &"m " & _s &"s"


end result:

 

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Kratchie's avatar
Kratchie
Frequent Visitor
3 years ago

Hi ValtteriN, 

 

When I created a new visual table and added only DURATION column with your script it worked like a charm. BUT when i'm adding more columns it stopped to work by any reason. Seems that only timestamps with seconds still works partly (only hours + seconds), but when it's whole hour. It doesnt work?! 

 

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Kratchie  hmm, 

    That seems quite weird. The first things that come to my mind are te following:

    1. Are there some other dimension information or slicers affecting the visual? 
    2. Is the data format similar in both your example and the data in question

    When I tried to re-create this the formula worked well even with whole hours and dates across different months: