Create a Custom Period Calendar in Power Query

[ChrisMendoza]

Why did I need this?

My organization uses what we call the Faculty / Staff Calendar for payroll. You can find them @ https://www.csuchico.edu/hr/calendars.shtml. Anyone notice anything odd?

The August period is defined as August 1^st - August 30^th and August 31^st is actually in the September period. I have been told the purpose of this is have the same number of working days in each period. I don't believe them.

Our custom period(s)

1. Fiscal Year defined as:
• July 1 through June 30
2. Monthly Periods are defined by the Chancellor's Office
• Officially we get an announcement saying the "calendar has been approved" generally, a couple of months before July
• It is literally a .pdf file!

Creating a Table in Power Query

The fundamentals are discussed in Chris Webb's BI Blog @ https://blog.crossjoin.co.uk/2016/06/03/creating-tables-in-power-bipower-query-m-code-using-table/.

This is where we are explicitly defining our Start of the Period and the End of the Period. Yes, unfortunately this is a manual process.

#table(
	type table
		[
			#"PeriodStart" = date,
			#"PeriodEnd" = date
		],
	{
		// Faculty/Staff Calendar 2018-2019
		{ #date ( 2018, 07, 01 ), #date ( 2018, 07, 31 ) },
		{ #date ( 2018, 08, 01 ), #date ( 2018, 08, 30 ) },
		{ #date ( 2018, 08, 31 ), #date ( 2018, 09, 30 ) },
		{ #date ( 2018, 10, 01 ), #date ( 2018, 10, 30 ) },
		{ #date ( 2018, 10, 31 ), #date ( 2018, 11, 29 ) },
		{ #date ( 2018, 11, 30 ), #date ( 2018, 12, 31 ) },
		{ #date ( 2019, 01, 01 ), #date ( 2019, 01, 30 ) },
		{ #date ( 2019, 01, 31 ), #date ( 2019, 02, 28 ) },
		{ #date ( 2019, 03, 01 ), #date ( 2019, 03, 31 ) },
		{ #date ( 2019, 04, 01 ), #date ( 2019, 04, 30 ) },
		{ #date ( 2019, 05, 01 ), #date ( 2019, 05, 30 ) },
		{ #date ( 2019, 05, 31 ), #date ( 2019, 06, 30 ) }
	}
)

Creating a Period Index

Add Column > Index Column > From 1.

Expanding the dates between

Add Column > Custom Column

List.Transform( { Number.From ( [PeriodStart] ) ..Number.From ( [PeriodEnd] ) }, each Date.From (_) )

Expand to New Rows

A list of all dates between

As you can see, now we have a list of all the calendar dates like a typical 'Calendar' table would have. The difference is we also have an Index number as well. Take a look at Rows 61 & 62.

Leveraging the Index

To really see how we leverage the Index values I'll need to add additional custom periods. Since I already have the Faculty / Staff Calendar for 2017-2018, I'll just add that too Advanced Editor.

We now have 24 unique custom periods; {1, 2, 3, ... ,22, 23, 24} over 730 rows.

Getting Fiscal Year Period Numbers {1, 2, ... , 11, 12}

The solution to this actually eluded me for a long time. My friend and co-worker, provided the answer to this.

Why don't you use the MOD function? - Elbert Chan

You can read about MOD ( ) @ https://docs.microsoft.com/en-us/powerquery-m/number-mod.

Add Column > Custom Column

if Number.Mod([Index], 12) = 0 then 12 else Number.Mod([Index], 12)

Now we have period numbers {1, 2, ... , 11, 12}.

You can see how this works at the change to the new fiscal year.

Getting the Period Name

In Excel, you can get the Month Name by using TEXT(MONTH([Serial Number]). We can not really do something like this here. Remember some dates are allocated to a different month/period. I'll use the example from above to demonstrate this.

Input of 8/31/2018

TEXT(MONTH(8/31/2018),"mmm")

Returns 'Aug'

The correct Period Name should be 'Sep' since 8/31/2018 belongs to that period in our custom calendar.

A custom function to SWITCH ( )

The fundamentals were originally found @ https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/ 

(input) =>

  let

  values = {

    {1, "Jul"},
    {2, "Aug"},
    {3, "Sep"},
    {4, "Oct"},
    {5, "Nov"},
    {6, "Dec"},
    {7, "Jan"},
    {8, "Feb"},
    {9, "Mar"},
    {10, "Apr"},
    {11, "May"},
    {12, "Jun"},
    {input, "Undefined"}

  },

  Result = List.First(List.Select(values, each _{0}=input)){1}

  in

  Result

Add Column > Custom Column

Combine 'PeriodNum' & 'PeriodName'

The campus reporting system report that I use shows the Period as '##-mmm'.

Add Column > Custom Column

Text.Combine({Text.PadStart(Number.ToText([PeriodNum]), 2, "0"),[PeriodName]}, "-")

The Fiscal Year Name/Number

Our fiscal year name for the periods July 2018 through June 2019 is 2018.

Add Column > Custom Column

if [PeriodNum] >= 7 then Date.Year([Date]) - 1 else Date.Year([Date])

The result

After formatting and ensuring data types, like [Date] is actually of type Date, I present the final Matrix visualization in Power BI