Generation of custom Calendars in Power Query

In my previous post I described how to build relative date functions in Power Query

Associated topic is a generation of Calendar table for Excel Data Model (aka PowerPivot) where better to have only necessary period of time. And certain list of dates in some cases, e.g. ends of last X month if we analyse month results.

Assume that we have model with sales of previous month. Quite often in such model we don’t need dates of previous of future year and when we refresh such model in the beginning of new month our Calendar should be refreshed as well.

I took idea of calendar generation from post on http://powerpivotpro.com – http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/, thanks to Matt Allington.

Functions List.Dates and List.Generate can help us get initial list of dates and then extend it to useful calendar with long month names, short month names etc.

Define Today as

Date.Today = Date.From(DateTime.FixedLocalNow())

Consider basic example – Last N days (excluding today)

List.Dates.LastNDays = List.Dates(Date.AddDays(Date.Today, -N), N, #duration(1,0,0,0))

For List.Dates we need to provide starting date and amount of days, #duration(1,0,0,0) gives us step size equal to 1 day.

Next examples calculate amount of days ‘on fly’:

List.Dates.R12MExcCurrentMonth = List.Dates(Date.StartOfMonth12MAgoExcCurMonth, Duration.Days(Date.EndOfLastMonth - Date.StartOfMonth12MAgoExcCurMonth), #duration(1,0,0,0)),

List.Dates.R12MIncCurrentMonth = List.Dates(Date.StartOfMonth12MAgoIncCurMonth, Duration.Days(Date.Today - Date.StartOfMonth12MAgoIncCurMonth), #duration(1,0,0,0)),

List.Dates.YearToDate = List.Dates(Date.StartOfCurrentYear, Duration.Days(Date.Today - Date.StartOfCurrentYear), #duration(1,0,0,0))

// Previous Year to Date

List.Dates.SamePeriodLastYear = List.Dates(Date.StartOfPreviousYear, Duration.Days(Date.AddYears(Date.Today, -1) - Date.StartOfPreviousYear) + 1, #duration(1,0,0,0))

Initial list of dates is a base for Extended Calendar.

Following example generates list of dates and extend it with fields like Year, Month name etc.

// Calendar for Rolling 12 months excluding current month

let

start = Date.From(Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.FixedLocalNow()), -12))),

end = Date.From(Date.EndOfMonth(Date.AddMonths(Date.From(DateTime.FixedLocalNow()), -1))),

duration = Duration.Days(end – start) + 1,

list_of_dates = List.Dates(start, duration, #duration(1,0,0,0)),

#”Table from List” = Table.FromList(list_of_dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Renamed Columns” = Table.RenameColumns(#”Table from List”,{{“Column1”, “Date”}}),

#”Added Year” = Table.AddColumn(#”Renamed Columns”, “Year”, each Date.Year([Date])),

#”Added Month No” = Table.AddColumn(#”Added Year”, “Month Number”, each Date.Month([Date])),

#”Added Day” = Table.AddColumn(#”Added Month No”, “Day”, each Date.Day([Date])),

#”Added Day Name” = Table.AddColumn(#”Added Day”, “Day Name”, each Date.ToText([Date],”ddd”)),

#”Added Month Short Name” = Table.AddColumn(#”Added Day Name”, “Month Short Name”, each Date.ToText([Date],”MMM”)),

#”Added Month Name” = Table.AddColumn(#”Added Month Short Name”, “Month Name”, each Date.ToText([Date],”MMMM”)),

#”Added Month Letter” = Table.AddColumn(#”Added Month Name”, “Month Letter”, each Text.Start([Month Short Name], 1)),

#”Added YearMonth” = Table.AddColumn(#”Added Month Letter”, “YearMonth”, each Number.ToText([Year]) & Number.ToText([Month Number])),

#”Changed Type” = Table.TransformColumnTypes(#”Added YearMonth”,{{“Date”, type date}, {“Year”, Int64.Type}, {“Month Number”, Int64.Type}, {“Day”, Int64.Type}, {“Day Name”, type text}, {“Month Short Name”, type text}, {“Month Name”, type text}, {“Month Letter”, type text}})

#”Changed Type”

You can change [start] and [end] to get calendar for another period.

#duration(1,0,0,0) defines step as 1 day. But it can be different, e.g. 31 day

Last 12 end of month – simple approach

let

start = Date.From(Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.FixedLocalNow()), -12))),

list_of_dates = List.Transform(List.Dates(start, 12, #duration(31,0,0,0)), Date.EndOfMonth),

#”Table from List” = Table.FromList(list_of_dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Renamed Columns” = Table.RenameColumns(#”Table from List”,{{“Column1”, “Date”}}),

#”Added Custom” = Table.AddColumn(#”Renamed Columns”, “Year”, each Date.Year([Date])),

#”Added Custom1″ = Table.AddColumn(#”Added Custom”, “Month Number”, each Date.Month([Date])),

#”Added Custom2″ = Table.AddColumn(#”Added Custom1″, “Day”, each Date.Day([Date])),

#”Added Custom3″ = Table.AddColumn(#”Added Custom2″, “Day Name”, each Date.ToText([Date],”ddd”)),

#”Added Custom4″ = Table.AddColumn(#”Added Custom3″, “Month Short Name”, each Date.ToText([Date],”MMM”)),

Custom1 = Table.AddColumn(#”Added Custom4″, “Month Name”, each Date.ToText([Date],”MMMM”)),

#”Added Custom5″ = Table.AddColumn(Custom1, “Month Letter”, each Text.Start([Month Short Name], 1)),

#”Added Custom6″ = Table.AddColumn(#”Added Custom5″, “Short Month-Year”, each [Month Short Name] & “-” & Number.ToText([Year])),

#”Added Custom7″ = Table.AddColumn(#”Added Custom6″, “Short Month-Short Year”, each [Month Short Name] & “-” & Text.End(Number.ToText([Year]), 2)),

#”Changed Type” = Table.TransformColumnTypes(#”Added Custom7″,{{“Date”, type date}, {“Year”, Int64.Type}, {“Month Number”, Int64.Type}, {“Day”, Int64.Type}, {“Day Name”, type text}, {“Month Short Name”, type text}, {“Month Name”, type text}, {“Month Letter”, type text}, {“Short Month-Year”, type text}, {“Short Month-Short Year”, type text}})

#”Changed Type”

If we want to include Today in such calendar, we need to add List.Combine

list_of_dates = List.Combine({List.Transform(List.Dates(start, 12, #duration(31,0,0,0)), Date.EndOfMonth), Today}),

I called this simple approach, because it is limited to 28-29 periods.

#duration(31,0,0,0) gives us step 31 days, which helps to get new month in every new row.

List.Transform( somelist , Date.EndOfMonth) applies Date.EndOfMonth to each element of list. (I plan to write separate post about this trick)

With such approach we can jump over Feb in leap year. So, don’t use it for big periods.

Serious way is usage of powerful, but difficult to understand, List.Generate function. To be honest, I still have feeling that don’t understand it completely. Thanks to patterns of Chris Webb, from which I got basic idea.

Following pattern generates list of last X ends of month with end of current month

let

start = DateTime.FixedLocalNow(),

months_qty = 24,

list_of_dates = List.Skip(List.Generate(

() => [i = 0],

each [i] <= months_qty,

each

[

Date = Date.From(Date.EndOfMonth(Date.AddMonths(Date.From(start), -[i] ))),

i= [i] + 1

each [Date]

))

list_of_dates

If you don’t need end of current month – define starting point as

() => [i = 1]

Last example

// Calendar of last X ends of months with today and period index

let

start = DateTime.FixedLocalNow(),

months_qty = 14,

list_of_dates = List.Combine({{Date.From(DateTime.FixedLocalNow())},

List.Skip(List.Generate(

() => [i = 1],

each [i] <= months_qty,

each

[

Date = Date.From(Date.EndOfMonth(Date.AddMonths(Date.From(start), -[i] ))),

i= [i] + 1

each [Date]

))}),