topic Re: Change date by time in DAX Commands and Tips

Change date by time

Anonymous — Sat, 25 Mar 2023 18:06:37 GMT

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.

Re: Change date by time

andrewpirie — Sat, 25 Mar 2023 18:58:20 GMT

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]

)

Re: Change date by time

tamerj1 — Sat, 25 Mar 2023 20:36:22 GMT

Hi Anonymous

you may try

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

Re: Change date by time

Anonymous — Sat, 25 Mar 2023 22:47:09 GMT

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

Re: Change date by time

Anonymous — Sat, 25 Mar 2023 22:48:22 GMT

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