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

Advertisements

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

  1. Thanks Gilbert! I am already utilizing the download file:
    One question:
    I noticed ‘Fiscal Year’ doesn’t change to the next year until Aug 1, which is ‘Fiscal Month’ 2 (when Fiscal Start Month parameter is set to 7) – Is this an error or am I missing something?

    Thanks again,
    – Kurt

    • Hi there Kurk,

      You were indeed correct and apologies for me not updating the PBIX file. I have since updated the file and validated that it now works as expected.

      If you can please download the file again from the link at the bottom of the blog post.

  2. Fantastic!
    Thank-you much for updating the file; I just downloaded it…and now I have another potential problem (hate to be a nuisance)…
    The table seems to only want to show 2016 values regardless of the year that is entered into the year parameter; ie: if I enter 2010 as the start year, the table still only shows 2016 dates (Jan1 – today). So, is this a user error or is the parameter not working properly?

    Thanks again,
    Kurt

    • Hi there Kurt, no problem at all.

      The issue was that in the steps under the Date Table in the Query Editor I had filtered out the rows for only 2016. Once I removed the filter it showed all the data.

      So if you can please re-download it, it will have the years from 2011 – 2016.

  3. Ahh – somehow I missed that…
    Okay, everything looks great now; I appreciate your quick responses.
    I will certainly be using this calendar table often – thanks for creating and posting the file.

    – Kurt

  4. Hi Gilbert,

    sure, I did refresh the dataset. Maybe it has to do with the fact that I am in different time zone (PST-9). But anyway – even if I do refesh during the end of my day, still the current day is missing. Weired …

    Best,
    Stefan

  5. Hi there Stefan,

    I found the issue, it was when I was building up the start and end date range.

    If you can go into the advanced editor and replace the following line

    #”Invoked FunctionSource” = Source(#date(#”Start Year”, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) – #date(#”Start Year”,1,1)), #duration(1, 0, 1, 0)),

    If you notice, for the last #duration there is a 1 second to last. So what this does is to include the current date as the last date.

    I will be uploading this workbook with the change also, if you wish to just download it again.

  6. Thanks Gilbert! There still is some sort of bug in the code. If I refresh the adjusted version you have deployed, it works alright. If I change the start date to e.g. 2015, it is again not working and the current day is missing…

    Best,
    Stefan

  7. Hi there I am not sure why that is happening. I just refreshed my copy now and it gave me the date of 03 Nov 2016.

    If you are still stuck I can email you directly, and possibly send me your copy to see what is happening?

  8. Hi, i used your fiscal table however i need an end date of 30th June 2025…it seems to only go up to Fiscal Dec 2017 year? Can we specify an end date for the date dimension table ?

    • Hi Garry, you can change the following line as shown below

      #”Invoked FunctionSource” = Source(#date(#”Start Year”, 1, 1), Duration.Days(DateTime.Date(Date.AddYears(DateTime.FixedLocalNow(),+10)) – #date(#”Start Year”,1,1)), #duration(1, 0, 0, 0)),

      Please notice above where there is the +10 this adds 10 years from today’s date.

      • Hey Gilbert, solved this with an end table parameter (Kudos to Steve Wheeler on PBI Community)

        Add an “End Year” parameter, and then amend the script line to:
        #”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)),

        Cheers Again.

  9. Hello Gilbert, thanks for that i get a token Identifier expected error in regards to #duration
    Here is cut and paste of the line

    #”Invoked FunctionSource” = Source(#date(#”Start Year”, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow(),+10)) – #date(#”Start Year”,1,1)), #duration(1, 0, 1, 0)),

  10. Hey Gilbert, just regard with copying your code text above and following the instructions, i still get an error saying “Token comma expected” . When i click show error it highlights the last word “Sorted”. Can you advise why?

    • Its on the last row, with the words “Sorted”, i just used your PBI file but if i copy and paste the above from your post i get the error.

  11. Hi Gilbert,

    Thank you for providing this. I have a token error but I’m going to review the comments above to see how to resolve it.
    However, I am unable to download your powerBI files. OneDrive seems to want to scan the files first and won’t let me download it.

    Sincerely,

    Kai

      • Hi Gilbert thanks for your reply!
        OneDrive isn’t loading for me. It keeps getting hung up at

        ### OneDrive
        “Unable to scan Creating Date Table with Fiscal Attributes.pbix for viruses
        OneDrive is unable to scan Creating Date Table with Fiscal Attributes.pbix for viruses.”
        ###

        Regarding the code above, it’s the same as some of the other “Token Comma expected”
        And it refers to #’Sorted Rows#

        If you could email me, that would be wonderful. Can you see my email from my name?

  12. Hi Gilbert, thank you for the great resource!

    Just as a note, there still seems to be an issue with the code above. I copied and pasted it and got the same token error on the final line.

    So, I downloaded the PBIX. Looks pretty good.

    I added an additional column: “Fiscal Year Display” to show “FY 14-15” or “FY 15-16″, which caused these additional lines to appear in the Advanced Editor:

    —-
    #”Add Fiscal Year Display” = Table.AddColumn(#”Renamed Columns1″, “Fiscal Year Display”, each “FY “&Text.PadStart(Text.End(Text.From([Fiscal Year]),2),2,”0″)&”-“&Text.PadStart(Text.End(Text.From([Fiscal Year]+1),2),2,”0″))
    in
    #”Add Fiscal Year Display”
    —-

    Except, my Fiscal Year starts Dec 1, and the results kept showing July 1.

    Digging through the Advanced Editor, I found these two lines:

    —-
    #”Added Conditional Column” = Table.AddColumn(#”Changed Type2″, “Fiscal Year”, each if [Month Number] <= 6 then [Year] else [Year]+1 ),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Fiscal Month", each if [Month Number] <= 6 then [Month Name] else [Month Name] ),
    —-
    The 6th month was hardcoded as the split for the new fiscal year, it seems. Regardless of what the variable "Fiscal Start Month" was set to, the fiscal year kept switching on July 1.

    I made this change:

    —-
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "Fiscal Year", each if [Month Number] <= #"Fiscal Start Month"-1 then [Year] else [Year]+1 ),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Fiscal Month", each if [Month Number] <= #"Fiscal Start Month"-1 then [Month Name] else [Month Name] ),
    —-

    Replacing the "6" with the results of the variable "Fiscal Start Month" less 1, and that seems to fix it, at least in the data table. I haven't tried implementing it yet.

    Great little bit of code to provide a starting point for anyone who needs a consistent DateTable to work with.

    Thanks!

    Dion

    • Thanks Dion for the great comments as well as explaining what you did to get it working.

      I do think that the font on the web page is causing the issue when copying and pasting and I will look into that today.
      As well as looking as to why I hard coded the Fiscal Years. And once I have updated that I will then let you know so that it can be dynamic going forward.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s