Forum Discussion

alexandrerich's avatar
alexandrerich
Regular Visitor
4 years ago
Solved

Double data

Hi, 

 

I want to find how many hours trailers is at my warehouse.   In my document I know when a driver come whit a trailer and when is go out whit different trailer. 

 

In my PowerBI, I find how to calculate hours, but I want to filter trailers was already gone. 

 

Do someone have any suggestion, 

 

Thank's

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi alexandrerich ,

     

    You can use merge.

     

    1.Duplicate the main table out of a new table, then retain the ID and time of the old table, and retain the ID and time of the new one, and then merge.

     

    2.Then expand the table

     

    3.Then add custom column to calculate the difference.

     

    4.Convert the column to total hours.

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    alexandrerich Not certain but seems like MTBF-type of problem. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
    The basic pattern is:
    Column = 
      VAR __Current = [Value]
      VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

      VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
    RETURN
      __Current - __Previous

     

    Might help with sample data as text and explanation of which column is which.

    • alexandrerich's avatar
      alexandrerich
      Regular Visitor
      4 years ago

      Hi Greg, , 

       

      The problem is my trailer OUT and IN is not in the same line.  See example

      Date INTrailer InTrailer Out

      Date out

      2021-09-11 8:004834

      4828

      2021-09-12 8:00

      2021-09-12 8:00154618362021-09-13 8:00
      2021-09-12 8:00482315462021-09-13 8:00
      2021-09-12 8:001625751892021-09-13 8:00
      2021-09-12 8:006141348342021-09-13 8:00

       

      And I dont want see trailer was go out. 

       

      I find how to have how many hour trailers is here at the present time, But I want to erase trailers is gone. 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi alexandrerich ,

         

        You can use merge.

         

        1.Duplicate the main table out of a new table, then retain the ID and time of the old table, and retain the ID and time of the new one, and then merge.

         

        2.Then expand the table

         

        3.Then add custom column to calculate the difference.

         

        4.Convert the column to total hours.

         

         

         

        Best Regards,

        Stephen Tao

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.