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, 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'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!

5 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    You can create a measure like this:

    • 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.

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 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]))))

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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!