Calculated calender with custom periods

[Nygaardsa]

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 to end of month, only last sunday in month). how can this be done?

The custom calender is solved by following code;

let
ChangedType = Table.TransformColumnTypes (IFS_PROD_Invoice,{{"I_DATE",type date}}),
MaxDate = Record.Field (Table.Max(ChangedType, "I_DATE"), "I_DATE"),
MinDate = Record.Field (Table.Min(ChangedType, "I_DATE"), "I_DATE"),
DaysElapsed = Number.From (MaxDate-MinDate),
DatesList = List.Dates (MinDate, DaysElapsed+1, Duration.From(1)),
RawDatesTable = Table.FromList (DatesList, Splitter.SplitByNothing(),
{"Date"}, null, ExtraValues.Error),
ChangedType1 = Table.TransformColumnTypes (RawDatesTable, {{"Date", type date}}),
InsertedDay = Table.AddColumn (ChangedType1, "Day", each Date.Day([Date]), type number),
InsertedMonth = Table.AddColumn (InsertedDay, "Month", each Date.Month([Date]), type number),
InsertedYear = Table.AddColumn(InsertedMonth, "Year", each Date.Year([Date]), type number)
in
    InsertedYear

Now I just need a way to add a column which sorts the dates into respective periods (from first monday to last sunday) named "2017-P01" etc... Any ideas?

[v-shex-msft · Accepted solution]

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]

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)

[v-shex-msft]

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