Forum Discussion
Anonymous
6 years agoNot applicable
Creating a shift model
I analyse user numbers, and I would like to divide the work day into early (6 am- 2 pm), late (2 pm- 10 pm) and night shifts (10 pm- 6 am). Creating the night shift is no problem because it runs eve...
Greg_Deckler
6 years agoCommunity Champion
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hard to be specific without source data. But, if you created an hours table like this:
R06_Table =
VAR __Hours =
SELECTCOLUMNS(
GENERATESERIES(1 , 12 , 1),
"Hour",
[Value] & ":00:00 "
)
VAR __Period =
SELECTCOLUMNS(
{ ("AM"),("PM") },
"AM/PM",
[Value]
)
VAR __HoursAMPM = GENERATEALL(__Hours, __Period)
VAR __Final =
SELECTCOLUMNS(
ADDCOLUMNS(
__HoursAMPM,
"Time",
TIMEVALUE([Hour] & [AM/PM])
),
"Time",
CONVERT(TODAY() & " " & [Time], DATETIME)
)
RETURN
__Final
You could create a Shift column like this:
Shift =
VAR __1stBegin = 9
VAR __2ndBegin = 17
VAR __3rdBegin = 1
VAR __Time = TIMEVALUE('R06_Table'[Time])
VAR __Hour = HOUR(__Time)
RETURN
SWITCH(TRUE(),
__Hour >= __1stBegin && __Hour < __2ndBegin,"First",
__Hour >= __2ndBegin || __Hour < __3rdBegin,"Second",
"Third"
)