Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I have a column with a date and time field formated as: 1/19/2022 2:05:16 PM
I need to calculate from this column whether it was Day Shift or Afternoon Shift. Day shift starts at 7 AM Monday - Friday and afternoon shift starts at 5:30 PM M - Thur and 4:30 PM on Fridays. Is there a way to do this? I have been looking but can't quite find a solution to my problem. Any direction would be appreciated.
I thought that this might work:
Solved! Go to Solution.
Hi @Opal55
Try this code to add a new column:
DvA =
Var _Time = TIMEVALUE(Tracking[Date/Tim])
Var _DN = WEEKDAY(Tracking[Date/Tim])
Var _MT = time(7,00,00)
Var _AF = if(_DN=6,time(16,30,00),time(17,30,00))
return
if(_Time>=_MT&&_Time<=_AF,"Day Shift","Afternoon Shift")
output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi @Opal55
Try this code to add a new column:
DvA =
Var _Time = TIMEVALUE(Tracking[Date/Tim])
Var _DN = WEEKDAY(Tracking[Date/Tim])
Var _MT = time(7,00,00)
Var _AF = if(_DN=6,time(16,30,00),time(17,30,00))
return
if(_Time>=_MT&&_Time<=_AF,"Day Shift","Afternoon Shift")
output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Perfect! Thank you so much for this!
Hi @Opal55 - It looks like the Datetime to Time comparison does not work. Time is treated as 1/1/1900 07:00:00.
please consider trying:
DvA = IF( AND( HOUR( Tracking[Date/Time] ) >= 7 , HOUR( Tracking[Date/Time] ) <= 16 ),"Day Shift","Afternoon Shift")
Or if Time is need, please split the Date Time into separate columns in Power Query instead.
Many thanks
Daryl
Hi @Opal55
try
DvA = If(And(TIMEVALUE(Tracking[Date/Time])>=TIME(07,00,00),TIMEVALUE(Tracking[Date/Time])<=TIME(16,00,00)),"Day Shift","Afternoon Shift")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |