Forum Discussion

dave_schultz's avatar
dave_schultz
Regular Visitor
3 years ago
Solved

Dynamic date/time change

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.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

4 Replies

  • vicky_'s avatar
    vicky_
    Super User
    3 years ago

    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())

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      dave_schultz
      Regular Visitor
      3 years ago

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

       

      David