Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query

I have been working on some new projects and one of the things that I found that was whilst you can find examples of creating Date tables, I could not find any examples of where the Date table included Fiscal Attributes such as Fiscal Month, Fiscal Quarter and Fiscal Year.

So in this blog post I am going to show how you can easily create your own date table with Fiscal Attributes in a few simple steps.

I have also created it in such a way that you can dynamically define your starting year, as well as your starting month for your fiscal attributes.

Below is a sample output of what I will explain below.

Creating Parameters

The first step is to create two parameters. These two parameters will be used to dynamically load our Start Year, as well as the Fiscal Start Month

Please NOTE you must create them with the same name and attributes. This is to ensure that the Power Query script will work correctly in the later steps.

This is how I created the Parameters.

• You can go into Edit Queries, then click on Manage Parameters and I put in the following for Start Year
  • 
  • As you can see above I put in the Name “Start Year”
  • I then changed the Type to Decimal Number
  • And then put in my Current Value of 2014 for the year I want it to start from.
• Next is the Parameters for Fiscal Start Month
  • 
  • As you can see above I put in the Name “Fiscal Start Month”
  • I then changed the Type to Decimal Number
  • And then put in my Current Value of 7
• Once completed I had the following under Queries
  • 

Creating Date Table with Fiscal Attributes

In this next step is where I used the Power Query language “M” to create my Date Table with the Fiscal Attributes.

You are welcome to read through what I have done, but all that you will need to do is the steps below and copy and paste to get it working. Nothing else needs to be modified in the code below, due to the fact that I worked it all out dynamically using the parameters we created above.

For those people who are interested after the code I will highlight what I did to create the Fiscal Attributes.

UPDATE: 10 Feb 2017

I have put some additional conditions in the code depending on when you want your End Date to be.

• If you want the End Date to be your current Date then you can leave the code as it is.
• If you want the End Date to be 4 years into the future and the last day of that year, then uncomment the second line. (Remember to comment out the previous line)
• And finally if you want the End Date to be the End Date Parameter then uncomment the third line. (Remember to comment out the previous line)

Now in the Query Editor complete the following to create the Date Table

• Click on New Source, then Blank Query
  • 
• Next I gave the Query a name from Query1 to Date
  • 
• Then in the Home Ribbon, click on Advanced Editor under the Query section
  • 
• This will then open the Advanced Editor as shown below.
  • 
• Now copy and paste in the code below.

  let
   Date = let
  Source = List.Dates,
  FiscalMonthCalc = 12-#"Fiscal Start Month",
  
  // 1. Uncomment this line if you want to get your end date being Today's Date of the Refresh
  #"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - #date(#"Start Year",1,1)), #duration(1, 0, 1, 0)),
  
  // 2. Uncomment this line below if you want your end date to be 4 years into the future
  //#"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(DateTime.Date(Date.AddDays(Date.EndOfYear(Date.AddYears(DateTime.FixedLocalNow(),+4)),-1)) - #date(#"Start Year",1,1)), #duration(1, 0, 1, 0)),
  
  // 3. Uncomment this line if you want to get your end date being the Parameter called "End Year"
  //#"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(#date(#"End Year", 12, 30) - #date(#"Start Year",1,1)), #duration(1, 0, 1, 0)),
  
  #"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
  #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"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 Name", each Date.ToText([Date],"MMM")),
  #"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
  #"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
  #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
  #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
  #"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
  #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
  #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
  #"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
  #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Quarter Year", "Short Year", "Year"}),
  #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Quarter Year", type text}, {"Year", Int64.Type}}),
   #"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "Fiscal Year", each if [Month Number] < #"Fiscal Start Month" then [Year] else [Year]+1 ),
   #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Fiscal Month", each if [Month Number] < #"Fiscal Start Month" then [Month Name] else [Month Name] ),
   #"Added Custom7" = Table.AddColumn(#"Added Conditional Column1", "Fiscal Month Sort Order", each Number.Mod(Date.Month([Date])+FiscalMonthCalc ,12)+1),
   #"Added Conditional Column3" = Table.AddColumn(#"Added Custom7", "Fiscal Quarter", each if [Fiscal Month Sort Order] >= 1 and [Fiscal Month Sort Order] <= 3 then "Q1" 
   else if [Fiscal Month Sort Order] >= 4 and [Fiscal Month Sort Order] <= 6 then "Q2" 
   else if [Fiscal Month Sort Order] >= 7 and [Fiscal Month Sort Order] <= 9 then "Q3" 
   else if [Fiscal Month Sort Order] >= 10 and [Fiscal Month Sort Order] <= 12 then "Q4" 
   else "Q Unknown" ),
  #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Fiscal Quarter Sort Number", each if [Fiscal Month Sort Order] >= 1 and [Fiscal Month Sort Order] <= 3 then "1" 
   else if [Fiscal Month Sort Order] >= 4 and [Fiscal Month Sort Order] <= 6 then "2" 
   else if [Fiscal Month Sort Order] >= 7 and [Fiscal Month Sort Order] <= 9 then "3" 
   else if [Fiscal Month Sort Order] >= 10 and [Fiscal Month Sort Order] <= 12 then "4" 
   else "Q Unknown" ),
   #"Changed Type3" = Table.TransformColumnTypes(#"Added Conditional Column4",{{"Fiscal Quarter Sort Number", Int64.Type}, {"Fiscal Month Sort Order", Int64.Type}, {"Fiscal Year", Int64.Type}}),
   #"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"Date", "Calendar Date"}, {"Month Number", "Calendar Month Number"}, {"Month Name", "Calendar Month Name"}, {"Quarter Number", "Calendar Quarter Number"}, {"Quarter Year", "Calendar Quarter Year"}, {"Short Year", "Calendar Short Year"}, {"Year", "Calendar Year"}})
  
  in
   #"Renamed Columns3",
   #"Duplicated Column" = Table.DuplicateColumn(Date, "Calendar Date", "Calendar Date - Copy"),
   #"Calculated Week of Year" = Table.TransformColumns(#"Duplicated Column",{{"Calendar Date - Copy", Date.WeekOfYear}}),
   #"Renamed Columns" = Table.RenameColumns(#"Calculated Week of Year",{{"Calendar Date - Copy", "Week Number of Year"}}),
   #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "Calendar Date", "Calendar Date - Copy"),
   #"Calculated Week of Month" = Table.TransformColumns(#"Duplicated Column1",{{"Calendar Date - Copy", Date.WeekOfMonth}}),
   #"Renamed Columns1" = Table.RenameColumns(#"Calculated Week of Month",{{"Calendar Date - Copy", "Week Number of Month"}})
  in
   #"Renamed Columns1"

• Then click Done
• Now you should see your Date Table with the Fiscal Attributes created.

Next I will show you how by changing the Fiscal Start Month how it dynamically changes the Fiscal Month Sort Order and Fiscal Quarter

• As with our current example it is setup with the Fiscal Start Month of 7.
• So the month of January will be the 7^th Fiscal Month starting from July.
  • 
  • As well the Fiscal Quarter will be Q3.
  • 
• Now I will change the Fiscal Start Month to 11
  • Click on the Fiscal Start Month and then change it from 7 to 11
  • As you can see below it currently is set to 7
    • 
  • And once done it is now changed to 11
    • 
• Now when you go back to your Date table you will see the following changes.
  • For the Month of January, the Fiscal Month Sort Order will be 3
    • 
  • And the Fiscal Quarter will be Q1
    • 
• You can try this for yourself and modify the parameters as required.

Technical overview on how I created the Fiscal Attributes

For the people who want to simply copy and paste and get it working you can skip to the conclusion, but for the people who want to understand how I did this please read below.

I had to come up with a way to get the right Fiscal Month Sort Order where the input could be dynamically driven. I did a fair bit of searching until I found someone who had done it in Excel, after which I modified it so that it would work with Power Query as well as bring back the right Fiscal Month Sort Order.

This is the code below, with the explanation afterwards.

• The first part is where I created the FiscalMonthCalc

  FiscalMonthCalc = 12-#”Fiscal Start Month”,
  

  • What I am doing above is taking the total months in a year (12) and subtracting the Fiscal Start Month Parameter
  • This will be used in the code below so that we can get the right output.
• Next is where I created the Fiscal Month Sort Order with the code below.

  Number.Mod(Date.Month([Date])+FiscalMonthCalc
  ,12)+1
  

  • As you can see above the first part highlighted in GREEN is where I am using the Mod (Divides two numbers and returns the remainder of the resulting number.)
  • In the next section is where I am getting the Month Number from the current Date row in the Date table highlighted in PURPLE
  • Now in order to get the number for our Mod, I then added this to my FiscalMonthCalc highlighted in RED
    • I had to do this in order to get the number for our Mod to be correct.
  • Then I next put in the divisor for our Mod which was 12 due to their being the 12 months of the year highlighted in ORANGE
  • The final part is where I had to increment the number by 1 to show the correct Fiscal Month Sort Order highlighted in BLUE

Then in order to create the Fiscal Quarter and Fiscal Quarter Sort Number I could then use the Fiscal Month Sort Order column created above.

This is the amazing thing with Power Query is that once I created my column I needed the next steps to create the Fiscal Quarter was as easy as defining our Quarters as we know which is every 3 months as shown below with the sample code.

#”Added Conditional Column3″ = Table.AddColumn(#”Added Custom7″, “Fiscal Quarter”, each if [Fiscal Month Sort Order] >= 1 and [Fiscal Month Sort Order] <= 3 then “Q1”

else if [Fiscal Month Sort Order] >= 4 and [Fiscal Month Sort Order] <= 6 then “Q2”

else if [Fiscal Month Sort Order] >= 7 and [Fiscal Month Sort Order] <= 9 then “Q3”

else if [Fiscal Month Sort Order] >= 10 and [Fiscal Month Sort Order] <= 12 then “Q4”

else “Q Unknown” ),

And because it is all driven off the Fiscal Month Sort Order it will always be correct due to being dynamically created.

Conclusion and download File

So in conclusion here is an easy way to create a Date table with Fiscal Periods that you can modify to your specific requirements.

Here is a link where you can download an example file: Creating Date Table with Fiscal Attributes.pbix