Change date by time

[Ruslan_Baranov]

Hello all.

I have two columns "Date" and "Time". I need to add a third column with the date "New Date".
The date should change not at 00:00 but at 06:00.

Example:
Weighing date March 1, 2023, time 11:39, "New date" March 1, 2023
Weighing date March 2, 2023, time 04:56, "New date" March 1, 2023

Thank you in advance for your cooperation.

[tamerj1 · Accepted solution]

Hi @Ruslan_Baranov 

you may try

New Date =
DATEVALUE (
'Table'[2nd Weight Date] + 'Table'[2nd Weight Time]
- TIME ( 6, 0, 0 )
)

[Ruslan_Baranov]

Thanks a lot for help, now it's working.

[andrewpirie]

I believe I understand this as for any times before 6am, the new date column should show the previous date, for times of 6am or later it should show the 2nd Weight date. Because this is a static property of the 2nd weight, and the 6am changeover time doesn't change, I suggest doing this in PowerQuery. 

Here's an expression to do this with a custom column in PowerQuery:

if Time.Hour([2nd Weight Time]) >= 6 then [2nd Weight Date] else Date.AddDays([2nd Weight Date], -1)

Here's the equivalent in DAX for a calculated column, but this would be less performant, so try the above first:

New Date =

IF(

    HOUR([2nd Weight Time]) < 6,

    'Weight Date and Times'[2nd Weight Date] - 1,

    'Weight Date and Times'[2nd Weight Date]

)

[Ruslan_Baranov]

Thanks a lot, but I tried this way and it's not workig.