Derive 24 hour shift date. Overlapping calendar date

[Selded]

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.

[ronrsnfld]

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:

[Selded]

No, i only need to subtract for the time between 12 mindnight to 6 am of next morning 

[ronrsnfld]

Using your other data set, and merely subtracting six hours from each datetime:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA7DoRADAPQq6CpkRjng3bS7QGQtkfc/xrLlGC3T4mc+DwbNmyWbW1RhuV7tGt9YJjCJMzqLtB4/caPwiHQ1brE6Ar5o6zk9F0dP8pDoCgEvSA1OR8o7EpTqBXG8nvrDGN0MFp1HrVZNWM4oc8G3hjz0onXHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
    #"Add Shift Date" = Table.AddColumn(#"Changed Type","Shift Date", 
        each Date.From(([Date] & [Time]) - #duration(0,6,0,0)), type date)
in
    #"Add Shift Date"

Please note that the date format is mm/dd/yyyy since I am in the US.

[ronrsnfld · Accepted solution]

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

[Selded]

@ronrsnfld , sorry i marked as solution but it did not work. i expect the yellow circled to be 04/06/2025 

[ronrsnfld]

Your adaptation will not work.

You must use the formula I posted, with all the parentheses.

Since you removed the outer set of parentheses, you are subtracting 6 hours from just the date, and not from the datetime.

Correct formula:

    Date.From(([Date] & [Time])-#duration(0,6,0,0))
             ^                                    ^ 

Your formula:

    Date.From([Date] & [Time])-#duration(0,6,0,0)

[Selded]

This worked, thank you

[Cookistador]

Hi @Selded 

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 
)

[Selded]

@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

[Selded]

@Cookistador dates between 9pm and 12 midnight returns the wrong shift date. could you assist please