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 )

     

     

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 )