Forum Discussion
Derive 24 hour shift date. Overlapping calendar date
I need a query to derive shift date from my data table. The expected Shift date is what i have highlighted in yellow, column shift 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
11 Replies
Hi 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
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
Can't you just subtract six hours from your datetime?
= Table.AddColumn(#"Changed Type", "Shift Date", each Date.From(([Date] & [Time]) -#duration(0,6,0,0)), type date)If you use the Add Custom Column dialog:
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
