Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculating time between two dates in Visual Matrix

Dear MS PBI Experts I am hoping someone can help me out here... I think this problem is relatively simply but I only started using PBI today... I have tidied up my data and presented it as a Matrix, ...
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    Create a measure as below:

    _Duration(Minutes)= 
VAR _group=CALCULATETABLE(VALUES('Table'[DateTime]),FILTER(ALL('Table'),'Table'[flight_id]=MAX('Table'[flight_id])))
var _takeoff=MINX(_group,'Table'[DateTime])
var _lande=MAXX(_group,'Table'[DateTime])
Return
DATEDIFF(_takeoff,_lande,MINUTE)

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
Anonymous's avatar
Anonymous
Not applicable
5 years ago

HI Anonymous 

I would use the below measure

Duration = 
Var seconds = DATEDIFF([landed],[takeoff],SECOND)
Var Hours   = INT([seconds]/3600)
Var Minutes = INT(MOD([seconds],3600)/60)
Var Seconds = MOD(MOD([seconds],3600),60)
Var HH = IF(LEN([Hours])=1,CONCATENATE("0",[Hours]),CONCATENATE("",[Hours]))
Var MM = IF(LEN([Minutes])=1,CONCATENATE("0",[Minutes]),CONCATENATE("",[Minutes]))
Var SS = IF(LEN([Seconds])=1,CONCATENATE("0",[Seconds]),CONCATENATE("",[Seconds]))
 
RETURN CONCATENATE([HH],CONCATENATE(":",CONCATENATE([MM],CONCATENATE(":",[SS]))))