Forum Discussion

HenryJS's avatar
HenryJS
Post Prodigy
5 years ago
Solved

Calculate Time Between

Hi all,   I have a column 'Rest Time' shown below which calculate the time between the start on one day and the end time on the previous day.   How can I change this column to calculate the time ...
Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • DataInsights's avatar
    DataInsights
    5 years ago

    HenryJS, try this:

     

    Rest = 
VAR vTimesheet =
    MAX ( Timesheet[Timesheet ID] )
VAR vMaxDate =
    MAX ( Timesheet[Date] )
VAR vMaxEndTime =
    MAX ( Timesheet[End Time] )
VAR vFutureDates =
    FILTER ( ALL ( Timesheet ), Timesheet[Timesheet ID] = vTimesheet && Timesheet[Date] > vMaxDate )
VAR vMinFutureDate =
    MINX ( vFutureDates, Timesheet[Date] )
VAR vFutureDate =
    FILTER ( vFutureDates, Timesheet[Date] = vMinFutureDate )
VAR vFutureStartTime =
    MINX ( vFutureDate, Timesheet[Start Tme] )
VAR vResult = 24 - vMaxEndTime + vFutureStartTime
RETURN
    IF ( ISBLANK ( vFutureStartTime ), BLANK(), vResult )

     

     

DataInsights's avatar
DataInsights
Super User
5 years ago

HenryJS, try this measure:

 

Rest = 
VAR vTimesheet =
    MAX ( Timesheet[Timesheet ID] )
VAR vMaxDate =
    MAX ( Timesheet[Date] )
VAR vMaxStartTime =
    MAX ( Timesheet[Start Tme] )
VAR vPrevDates =
    FILTER ( ALL ( Timesheet ), Timesheet[Timesheet ID] = vTimesheet && Timesheet[Date] < vMaxDate )
VAR vMaxPrevDate =
    MAXX ( vPrevDates, Timesheet[Date] )
VAR vPrevDate =
    FILTER ( vPrevDates, Timesheet[Date] = vMaxPrevDate )
VAR vPrevEndTime =
    MAXX ( vPrevDate, Timesheet[End Time] )
VAR vResult = 24 - vPrevEndTime + vMaxStartTime
RETURN
    IF ( ISBLANK ( vPrevEndTime ), BLANK(), vResult )

 

 

  • HenryJS's avatar
    HenryJS
    Post Prodigy
    5 years ago

    DataInsights  Hi,

     

    Is it possible to switch the rest time so it is calculating the time between End time on 14/09 and Start time on 15/09.

     

    So rest time on 14/09 on your image below, would be 15:00:00

     

    thanks,

    • DataInsights's avatar
      DataInsights
      Super User
      5 years ago

      HenryJS, try this:

       

      Rest = 
VAR vTimesheet =
    MAX ( Timesheet[Timesheet ID] )
VAR vMaxDate =
    MAX ( Timesheet[Date] )
VAR vMaxEndTime =
    MAX ( Timesheet[End Time] )
VAR vFutureDates =
    FILTER ( ALL ( Timesheet ), Timesheet[Timesheet ID] = vTimesheet && Timesheet[Date] > vMaxDate )
VAR vMinFutureDate =
    MINX ( vFutureDates, Timesheet[Date] )
VAR vFutureDate =
    FILTER ( vFutureDates, Timesheet[Date] = vMinFutureDate )
VAR vFutureStartTime =
    MINX ( vFutureDate, Timesheet[Start Tme] )
VAR vResult = 24 - vMaxEndTime + vFutureStartTime
RETURN
    IF ( ISBLANK ( vFutureStartTime ), BLANK(), vResult )