topic Replace Time of specific Datetime in DAX Commands and Tips

Replace Time of specific Datetime

Gresi — Mon, 16 Sep 2019 09:04:29 GMT

Hi at all,

i have a table with a column named start that represents a Datetime. Format -> 01.07.2019 05:50:00

I also have a Slicer where i can select a timespan. In this case from 6am to 2pm.

Now when start is less than 6 i want to replace the time 05:50:00 with 06:00:00.

Start should look like 01.07.2019 06:00:00 now.

It would be nice if someone can help me.

Thanks in advance,

Gresi

Re: Replace Time of specific Datetime

Anonymous — Mon, 16 Sep 2019 11:10:22 GMT

Hi,

You should first extract the time from the Datetime Value. For doing this Select the column -> Go to Modelling -> Change Datetime format (HH:MMS). This would show the Column as Time Value. Now, Create a new column with the below formula

Once Done, Go to Modelling and change the Format back to Datetime (DD/MM/YYYY HH:MMS) as the Output Datatype from the above would be of type Text.

New_Column:= Date(Year(Date), Month(Date), Day(Date)) &IF(HOUR(Date[Time])< 6, "06:00:00",Date[Time]))