Forum Discussion

Fcoatis's avatar
Fcoatis
Post Patron
8 years ago
Solved

Next monday

Hello everyone,

 

just a little help with this. I need to calculate a date as follows: if today is monday, keep it. If not return the next monday. Thanks in advance. Best wishes

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    II guess you mean a calculated column?

     

    Something like:

     

    NextMonday = DATEADD(Dates[Date],MOD(7-WEEKDAY(Dates[Date],3),7),DAY)

4 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    In Power Query, you can use Date.EndOfWeek with Day.Tuesday as second parameter (which is the start of the week).

     

    Example query:

     

    let
    Source = #table(type table[Date = date],List.Zip({List.Dates(#date(2017,12,1),31,#duration(1,0,0,0))})),
    #"Calculated End of Week" = Table.TransformColumns(Source,{{"Date", each Date.EndOfWeek(_,Day.Tuesday), type date}})
in
    #"Calculated End of Week"

     

    You may also be interested in Bemint - Power Query (M) Functions Dashboard.

     

    Happy holidays!

    • Fcoatis's avatar
      Fcoatis
      Post Patron
      8 years ago

      Thank you very much. But I was thinking of a measure.

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        8 years ago

        II guess you mean a calculated column?

         

        Something like:

         

        NextMonday = DATEADD(Dates[Date],MOD(7-WEEKDAY(Dates[Date],3),7),DAY)