Hello everyone,
I’m trying to Calculate regular/evening/night hours.
I tried to use the function Calculate, but it doesn’t work for me.
Regular hours: 8AM - 6PM
Evening hours: 6PM - 12PM
Night hours: 12PM - 8AM
Outcome: A shift from 3PM - 11PM should have 3 Regular hours and 5 Evening hours
Help is very appreciated
Preventief.pbix (96.1 KB)
Hi @krampit,
Interesting problem to solve. Are the start and end times of each shift fixed?
Regards,
Thank you for your reply.
Unfortunately the times of the shifts aren’t fixed.
It can happen that multiple employees work in the evening, but have different start/end times.
It can also happen that in week 1 the Start/End time is 11AM - 4PM and one week later from 10AM-5PM.
It all depends of the Sales-table.
Hello @krampit,
Thank You for posting your query onto the Forum.
Firstly, the file which you’ve provided in that you haven’t converted your “Start Time” and “End Time” fields into a proper “Time” data type inside the Power Query. And therefore the solution which I’m going to provide will not work in your file directly unless and untill you convert them into a proper “Time” data type.
This I came to know after my several attempts where I was not able to match the “Start Time” and “End Time” fields with the measure I was trying to create in order to achieve the results. This is how it looks like at the back end of the data. Below is the screenshot provided for the reference -
And therefore, I created my own new file where both these fields are in a proper “Time” data type.
Once this is done, you can write the below provided measures to break the “Total Hours” into the categories - “Regular Hours”, “Evening Hours” and “Night Hours”.
1. Regular Hours - Harsh =
IF(
SELECTEDVALUE( Sales[Date Time Start] ) < SELECTEDVALUE( Sales[Date Time End] ) ,
IF(
SELECTEDVALUE( Sales[Date Time Start] ) >= TIME( 8 , 0 , 0 ) &&
SELECTEDVALUE( Sales[Date Time End] ) <= TIME( 18 , 0 , 0 ) ,
SELECTEDVALUE( Sales[Date Time Start] ) - SELECTEDVALUE( Sales[Date Time End] ) ,
IF(
SELECTEDVALUE( Sales[Date Time Start] ) >= TIME( 8 , 0 , 0 ) &&
SELECTEDVALUE( Sales[Date Time Start] ) <= TIME( 18 , 0 , 0 ) ,
SELECTEDVALUE( Sales[Date Time Start] ) - TIME( 18 , 0 , 0 ) ,
IF(
SELECTEDVALUE( Sales[Date Time End] ) >= TIME( 8 , 0 , 0 ) &&
SELECTEDVALUE( Sales[Date Time End] ) <= TIME( 18 , 0 , 0 ) ,
TIME( 8 , 0 , 0 ) - SELECTEDVALUE( Sales[Date Time End] ) ) ) ) )
3. Evening Hours - Harsh =
IF(
SELECTEDVALUE( Sales[Date Time Start] ) >= TIME( 18 , 0 , 0 ) &&
SELECTEDVALUE( Sales[Date Time Start] ) <= ( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) ,
SELECTEDVALUE( Sales[Date Time Start] ) - ( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) ,
IF(
SELECTEDVALUE( Sales[Date Time End] ) >= TIME( 18 , 0 , 0 ) &&
SELECTEDVALUE( Sales[Date Time End] ) <= ( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) ,
SELECTEDVALUE( Sales[Date Time End] ) - TIME( 18 , 0 , 0 ) ) )
5. Night Hours - Harsh =
IF(
SELECTEDVALUE( Sales[Date Time Start] ) >= TIME( 0 , 0 , 0 ) &&
SELECTEDVALUE( Sales[Date Time Start] ) < TIME( 8 , 0 , 0 ) ,
SELECTEDVALUE( Sales[Date Time Start] ) - TIME( 8 , 0 , 0 ) ,
IF(
SELECTEDVALUE( Sales[Date Time End] ) >= TIME( 0 , 0 , 0 ) &&
SELECTEDVALUE( Sales[Date Time End] ) < TIME( 8 , 0 , 0 ) ,
SELECTEDVALUE( Sales[Date Time End] ) - TIME( 0 , 0 , 0 ) ) )
Once these measure’s are created, you can follow the same process to create the measure’s for “Grand Totals” where in your previous post @jafernandezpuga had provided the formula for that.
After creating all the measure’s, you’ll be able to achieve the desired results. Below is the screenshot of the final results provided for the reference -
This one was indeed an interesting problem to solve especially the “Regular Hours” section which was one tough nut to crack.
I’m also attaching the working of the PBIX file for the reference purposes.
Hoping you find this useful and meets your requirements that you’ve been looking for. 
Thanks and Warm Regards,
Harsh
Shift Hours - Harsh.pbix (27.3 KB)
Hi @krampit and @Harsh,
This is the measure I was doing for Regular hours.
Regular Hours =
VAR _MinHourRegular = TIME( 08, 00, 00 )
VAR _MaxHourRegular = TIME( 18, 00, 00 )
VAR _StartDateTime = SELECTEDVALUE( Sales[Date TIME Start] )
VAR _StartDate = INT( _StartDateTime )
VAR _StartTime = _StartDateTime - _StartDate
VAR _EndDateTime = SELECTEDVALUE( Sales[Date TIME End] )
VAR _EndDate = INT( _EndDateTime )
VAR _EndTime = _EndDateTime - _EndDate
VAR _StartDateTimeRegular = _StartDate + _MinHourRegular
VAR _EndDateTimeRegular = _StartDate + _MaxHourRegular
VAR _Result = SWITCH(
TRUE(),
(
_StartTime >= _MinHourRegular &&
_StartTime <= _MaxHourRegular
) &&
(
_EndTime >= _MinHourRegular &&
_EndTime <= _MaxHour Regular
),
_EndDateTime - _StartDateTime,
(
_StartTime < _MinHourRegular &&
_EndTime > _MaxHour Regular
),
_EndDateTimeRegular - _StartDateTimeRegular,
(
_StartTime < _MinHourRegular &&
_EndTime > _MinHourRegular &&
_EndTime < _MaxHour Regular
),
_EndDateTime - _StartDateTimeRegular,
(
_StartTime > _MinHourRegular &&
_StartTime < _MaxHour Regular &&
_EndTime > _MaxHour Regular
),
_EndDateTimeRegular - _StartDateTime,
BLANK()
)
RETURN
_Result
The first case is when the work period is within the start and end of regular hours. The difference between the end and the beginning of the work period is returned.
The second case is when the work period begins before the start of regular hours and ends after the end of regular hours. The difference in hours between the end of regular hours and the beginning of regular hours is returned.
The third case is when the work period begins before the start of regular hours and ends before the end of regular hours. The difference between the end of the work period and the start of regular hours is returned.
The last case is when the work period begins after the start of regular hours and ends after the end of regular hours. The difference between the end of regular hours and the start of the work period is returned.
This is the measure for the total regular hours:
SUM Total Regular Hours =
VAR Seconds1 = SUMX( Sales, SECOND( [Regular Hours] ) )
VAR Minutes1 = SUMX( Sales, MINUTE( [Regular Hours] ) )
VAR Hours1 = SUMX( Sales, HOUR( [Regular Hours] ) )
VAR Seconds2 = MOD( Seconds1, 60 )
VAR Minutes2 = MOD( TRUNC( Seconds1 / 60 ) + Minutes1, 60 )
VAR Hours2 = Hours1 + TRUNC( ( TRUNC( Seconds1 / 60 ) + Minutes1 ) / 60 )
RETURN
IF( OR( Hours2<>0, Minutes2<>0 ), Hours2 & “:” & FORMAT( Minutes2, “00” ))
Regards,
Thank you @Harsh.
This solution brings me immediately to another challenge 
I want to calculate the wages of the employees and I need the Hours in Integer-format for that instead of Text-format…
I’ll try to figure out
If you need an integer output, I wonder if something as simple as
Hours2 + Minutes2/60 + Seconds2/3600 will work?
(the replacement to the RETURN line within the measure that @jafernandezpuga designed for formatted output)
So I appreciate this is an old post now, however I’ve found this last night and it was actually really helpful. The solution posted by Harsh does work for the specific scenario stated, however, mine was slightly modified from this and as a result found a few issues with the measures Harsh provided.
I thought it might be helpful for anyone else who comes across this post to share my development based on what Harsh originally provided.
For context I’m trying to achieve the same sort of outcome, however I wanted to split down to an early, day, evening and night period. This allows for consideration of the night rate potentially changing to a different day such as on a weekend where the rate could change.
I also found that shifts which spanned a period, such as one that started at 0700 in the morning and finished at 1900 in the evening, would be picked up in the evening totals and the new early totals I’d added in but not in the daytime totals as it ignores it due to it spanning the set time period.
If you feel this would be helpful then I recommend you download what Harsh provided and just tweak the measures slightly. I’ve not needed to change his total measures at all and I just added in two new measures for the early and early totals, with the early totals being a duplicate of the regular totals pointed to the early calculations.
For reference, my table is called Calendar Data 2 instead of sales, and the column Date Time Start is just Start-Time and Date Time End is End-Time, so you’ll need to change this if you drop it directly into Harsh’s model. I also reference a Start-Date and End-Date in there so it will be helpful if you have this too as this isn’t used in Harsh’s model.
This isn’t a straight drag and drop - I’m sharing more so you have the logic required to point to your own data.
Hope this helps someone!
11. Early Hours - Lookup =
IF(
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) < SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) ,
IF(
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) >= TIME( 0 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) <= TIME( 8 , 0 , 0 ) ,
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) - SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) ,
IF(
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) >= TIME( 0 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) <= TIME( 8 , 0 , 0 ) ,
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) - TIME( 8 , 0 , 0 ) ,
IF(
SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) >= TIME( 0 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) <= TIME( 8 , 0 , 0 ) ,
TIME( 0 , 0 , 0 ) - SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) ) ) ) )
1. Regular Hours - Lookup =
IF(
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) <= TIME( 8 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) >= TIME( 18 , 0 , 0 ) ,
TIME( 18 , 0 , 0 ) - TIME( 8 , 0 , 0 ),
IF(
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) <= TIME( 18 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) >= TIME( 0 , 0 , 0 ) &&
SELECTEDVALUE('Calendar Data 2'[End-Date]) <> SELECTEDVALUE('Calendar Data 2'[Start-Date]),
TIME( 18 , 0 , 0 ) - SELECTEDVALUE( 'Calendar Data 2'[Start-Time]),
IF(
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) < SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) ,
IF(
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) >= TIME( 8 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) <= TIME( 18 , 0 , 0 ) ,
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) - SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) ,
IF(
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) >= TIME( 8 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) <= TIME( 18 , 0 , 0 ) ,
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) - TIME( 18 , 0 , 0 ) ,
IF(
SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) >= TIME( 8 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) <= TIME( 18 , 0 , 0 ) ,
TIME( 8 , 0 , 0 ) - SELECTEDVALUE( 'Calendar Data 2'[End-Time]) ) ) ) ) ) )
3. Evening Hours - Lookup =
IF(
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) <= TIME( 18 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) >= TIME( 0 , 0 , 0 ) &&
SELECTEDVALUE('Calendar Data 2'[End-Date]) <> SELECTEDVALUE('Calendar Data 2'[Start-Date]),
( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) - TIME( 18 , 0 , 0 ),
IF(
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) >= TIME( 18 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) <= ( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) ,
SELECTEDVALUE( 'Calendar Data 2'[Start-Time] ) - ( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) ,
IF(
SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) >= TIME( 18 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) <= ( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) ,
SELECTEDVALUE( 'Calendar Data 2'[End-Time] ) - TIME( 18 , 0 , 0 ) ) ) )
5. Night Hours - Lookup =
IF(
SELECTEDVALUE( 'Calendar Data 2' [Start-Time] ) >= TIME( 0 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2' [Start-Time] ) < TIME( 8 , 0 , 0 ) &&
SELECTEDVALUE('Calendar Data 2'[End-Date]) <> SELECTEDVALUE('Calendar Data 2'[Start-Date]),
SELECTEDVALUE( 'Calendar Data 2' [Start-Time] ) - TIME( 8 , 0 , 0 ) ,
IF(
SELECTEDVALUE( 'Calendar Data 2' [End-Time] ) >= TIME( 0 , 0 , 0 ) &&
SELECTEDVALUE( 'Calendar Data 2' [End-Time] ) < TIME( 8 , 0 , 0 ) &&
SELECTEDVALUE('Calendar Data 2'[End-Date]) <> SELECTEDVALUE('Calendar Data 2'[Start-Date]),
SELECTEDVALUE( 'Calendar Data 2' [End-Time] ) - TIME( 0 , 0 , 0 ) ) )