Forum Discussion

HenryJS's avatar
HenryJS
Post Prodigy
5 years ago
Solved

Calculate Time Between

Hi all,

 

How can create the column 'Break' below?

 

It calculates the time between the end of the shift and start of the next one. They can be on different dates but have to be the next day worked (date column).

 

This also has to correlate to Timesheet ID.

 

Timesheet IDDateStartEndBreak
14469902/09/202007:3017:000
14469903/09/202007:3017:0014:30
14469906/09/202007:3017:0014:30
14470001/0908:0017:000
14470005/0908:0017:0009:00
Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    Hi HenryJS ,

     

    Start_time = 
VAR __start = MAX(Sheet7[Start])
RETURN
DATE(2020, 1, 2) + __start
    End_time = 
VAR __end = 
CALCULATE(
    MAX([End]),
    FILTER(
        ALL(Sheet7),
        Sheet7[Timesheet ID] = MAX(Sheet7[Timesheet ID]) && Sheet7[Date] < MAX(Sheet7[Date]) 
    )
)
RETURN
IF(
    __end <> BLANK(),
    DATE(2020,1,1) + __end,
    BLANK()
)
    Measure 2 = 
IF(
    [End_time] <> BLANK(),
   [Start_time] - [End_time],
    TIME(0,0,0)
)

     

    Best regards,
    Lionel Chen

    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
    5 years ago

    HenryJS 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 __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
    RETURN
      __Current - __Previous

     

    In your case you would use DATEDIFF between your row and the previous row once you extract the necessary values.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    HenryJS , Try new columns like

     

    create new columns
    start time = [Date] + [start]
    End time = [Date] + [end]

    Break = maxx(flter(Table, [Timesheet ID] =earlier([Timesheet ID]) && [Date]<earlier([date])),[End time]) -[start time]

    • HenryJS's avatar
      HenryJS
      Post Prodigy
      5 years ago

      amitchandak thanks that works!

       

      However on the first instance it calculates a break time even though there's no shift before?

       

      The one highlighted red should be 0

       

       

       

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    Hi HenryJS ,

     

    Start_time = 
VAR __start = MAX(Sheet7[Start])
RETURN
DATE(2020, 1, 2) + __start
    End_time = 
VAR __end = 
CALCULATE(
    MAX([End]),
    FILTER(
        ALL(Sheet7),
        Sheet7[Timesheet ID] = MAX(Sheet7[Timesheet ID]) && Sheet7[Date] < MAX(Sheet7[Date]) 
    )
)
RETURN
IF(
    __end <> BLANK(),
    DATE(2020,1,1) + __end,
    BLANK()
)
    Measure 2 = 
IF(
    [End_time] <> BLANK(),
   [Start_time] - [End_time],
    TIME(0,0,0)
)

     

    Best regards,
    Lionel Chen

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