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.

  • 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 7th 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