Calculating time between two dates in Visual Matrix

[Anonymous]

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, where each row represents a flight and each column is take-off and land. In Excel I can subtract one number by the other to get the duration of the journey... but how do I do this on PBI Matrix Visulisation... any idea? Thank you all!

[v-kelly-msft · Accepted solution]

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]

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]))))

[camargos88]

Hi @Anonymous ,

You can create a measure like this:

[Anonymous]

Thank you so much for your help everyone! ... but I struggled to get it to work.

A little background on the raw data, every single flight_id corresponds to two seperate row: one for take off and one for landing - see snapshot below:

(Stamp field just combine date and time field.)

So first I pulled the data into a Matrix visulisation to get it in the format below so I can see the correspnoding landing and take off time for each fligth_id. But now I'm strugglign to find the duration as the SUMX function doesn't recognise takeoff and landing as a variable in the tabe...

Thank you everyone once again for your help!

[camargos88]

Hi @Anonymous ,

See the attached file, I've created 2 examples. 

First with your data model and other using pivot table.