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 1st - August 30th and August 31st 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)
- Fiscal Year defined as:
- July 1 through June 30
- 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
Resident Rockstar
