Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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
    6 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
6 years ago

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's avatar
camargos88
Community Champion
6 years ago

Hi Anonymous ,

 

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

First with your data model and other using pivot table.