Forum Discussion
Derive 24 hour shift date. Overlapping calendar date
If you subtract from all the datetimes, the results will be the same, and the equation is simpler.
eg. 4/6/2025 6AM - 6hrs => 4/6/2025
5/6/2025 5:59AM - 6hr => 4/6/2025
Super UserHi Anonymous
The following dax code for a calculated column shoudl work
ShiftDate =
VAR EventDateTime = [Date] + [Time]
VAR ShiftStartTime = TIME(6, 0, 0)
VAR ShiftEndTime = TIME(17, 59, 59)
VAR TimeOnly = TIME(HOUR(EventDateTime), MINUTE(EventDateTime), SECOND(EventDateTime))
RETURN
IF (
TimeOnly >= ShiftStartTime && TimeOnly <= ShiftEndTime,
[Date],
[Date] - 1
)
Cookistador thank you, I will try this. I wanted this done in power qury because i will need the shiftdate column for a future merge. Any tips how to achieve this in power query
- Cookistador
I'm very sorry, You are a right, we are on Power query board 🙂
You can achieve that with the following M code for a custom culomn
let
timeValue = [Time],
dateValue = [Date],
shiftStart = #time(6, 0, 0),
shiftEnd = #time(17, 59, 59),
shiftDate = if timeValue >= shiftStart and timeValue <= shiftEnd then
dateValue
else
Date.AddDays(dateValue, -1)
in
shiftDate