Dynamic date/time change

[dave_schultz]

Hi there,

Everyday, I want to add a dynamic filter looking at two day's ago with the the earliest time. For example for this morning: today is 2022-12-08. I want to get: 2022-12-08-0-0-0. Here is what i got: 

let
Source = DateTime.LocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Added Custom1" = Table.AddColumn(#"Converted to Table", "Custom.1", each Date.AddDays([Column1],-2)),
#"date and time" = #"Added Custom1"{0}[Custom.1]
in
#"date and time"

It's pulling the current 2 day's ago date and time. I always want midnight.

[dave_schultz · Accepted solution]

Hi @vicky_ 

Thanks for responding. Looks like that returns only the date.

[Anonymous · Accepted solution]

Hi @dave_schultz ,

Try the Date.StartOf Day function. It returns the start of the day represented by dateTime. dateTime must be a date, datetime, or datetimezone value.

let
Source = DateTime.LocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Added Custom1" = Table.AddColumn(#"Converted to Table", "Custom.1", each Date.StartOfDay(Date.AddDays([Column1],-2)))
in
    #"Added Custom1"

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dave_schultz]

Hi @Anonymous - Yes, this answered my question. Thank you!

David

[dave_schultz · Accepted solution]

Hi @vicky_ 

Thanks for responding. Looks like that returns only the date.

[vicky_]

You could try converting the date time to just a date (I think that would make it midnight) with something like: 

DateTime.Date(DateTime.LocalNow())