Forum Discussion

Nygaardsa's avatar
Nygaardsa
Frequent Visitor
8 years ago
Solved

Calculated calender with custom periods

Hi, I am trying to create a calculated calender which only include dates from invoiced dates, and then adding a custom column with corresponding custom period based on last sunday in month (similar ...
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI Nygaardsa,

     

    You can try to use below formula add custom column to find out last Sunday of each month:

     

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Last Sunday", each Date.AddDays(Date.EndOfMonth([Date]), -1* Date.DayOfWeek(Date.EndOfMonth([Date]),Day.Sunday)))

    Full query:

    let
    Source= List.Dates(#date(2010,1,1),1200,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Last Sunday", each Date.AddDays(Date.EndOfMonth([Date]), -1* Date.DayOfWeek(Date.EndOfMonth([Date]),Day.Sunday))),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}, {"Last Sunday", type date}})
in
    #"Changed Type"

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

HI Nygaardsa,

 

You can try to use below formula add custom column to find out last Sunday of each month:

 

#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Last Sunday", each Date.AddDays(Date.EndOfMonth([Date]), -1* Date.DayOfWeek(Date.EndOfMonth([Date]),Day.Sunday)))

Full query:

let
    Source= List.Dates(#date(2010,1,1),1200,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Last Sunday", each Date.AddDays(Date.EndOfMonth([Date]), -1* Date.DayOfWeek(Date.EndOfMonth([Date]),Day.Sunday))),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}, {"Last Sunday", type date}})
in
    #"Changed Type"

Regards,

Xiaoxin Sheng

  • Nygaardsa's avatar
    Nygaardsa
    Frequent Visitor
    8 years ago

    Thanks alot, this is working great! However, can I trouble you with a new question? How can one find the corresponding first monday in period, for instance monday the 27.08.2018 in periode 27.08.2018 (first monday after last sunday in period 08) to 30.09.2018 (last sunday in period 09)

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Nygaardsa,

       

      In fact, you need to combo use date functions to get date which you wanted and calculate the offset of specific day of week.

      let
    Source= List.Dates(#date(2010,1,1),1200,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Last Sunday", each Date.AddDays(Date.StartOfMonth([Date]), if Date.DayOfWeek(Date.StartOfMonth([Date]),Day.Sunday)<>0 then 7-Date.DayOfWeek(Date.StartOfMonth([Date]),Day.Sunday)+1 else Date.DayOfWeek(Date.StartOfMonth([Date]),Day.Sunday)+1)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}, {"Last Sunday", type date}})
in
    #"Changed Type"

      Date functions

       

      Regards,

      Xiaoxin Sheng