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.comhttp://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

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”}}),

#”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}})

in

#”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

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

in

#”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

[

i= [i]        + 1

],

each [Date]

))

in

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

[

i= [i]        + 1

],

each [Date]

))}),

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

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

#”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}}),

in

To get more functions, seek for List.Dates.* functions in https://github.com/IvanBond/pquery

And feel free to contribute your functions.

May the Force of Power Query be with you!

## 2 thoughts on “Generation of custom Calendars in Power Query”

1. Hartmut says:

Some special columns for a custom calendar: