Date Dimension Script with Fiscal Year

Bradley Schacht

Bradley Schacht is a Cloud Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

7 Responses

  1. Mike says:

    Nice script – thanks for sharing!

  2. Chunai says:

    Thanks Bradley for sharing these scripts!
    But I’m getting an issue while executing the ‘Date Dimension Add Fiscal Dates.sql’ script.
    In my company the Fiscal year is calculated from July 1st to June 30th.
    In the DimDate.sql script , I’ve declared the @StartDate DATE = ’01/01/2012′ and @EndDate DATE = ’01/01/2016′
    And in the Fiscal date script I’ve set the values like-
    SET @dtFiscalYearStart = ‘July 01, 1995’
    SET @FiscalYear = 1995
    SET @LastYear = 2025
    SET @FirstLeapYearInPeriod = 1996

    Ideally the output should come as –
    From dates 01/01/2012 to 06/30/2012, Fiscal year should be 2012
    From dates 07/01/2012 to 06/30/2013, Fiscal year should be 2013
    From dates 07/01/2013 to 06/30/2014, Fiscal year should be 2014 and so on….

    But somehow after executing the script its showing a change in the Fiscal year starting from 07/05.
    So for fiscal year 2013, its starting from 07/05/2012, instead of 07/01/2012
    Fiscal year 2014 is starting from 07/04/2013, FY 2015 starts from 07/03/2014….

    Could you please help me regarding this issue?

  3. Fasty says:

    Yeah, the variables you set will not correctly fill out the rest of the script in terms of the fiscal days, months and quarters. It only half works correctly. For example, if you set the start of the fiscal to February 2nd, 2014, the end of the fiscal month is actually on week 5 rather than week 4. Thus, it breaks the whole structure of 4-5-4.

  4. sreedhar says:

    I need a fiscal year from 2015-10-01 to 2016-09-30 ? can u help me this question ASAP.

  5. Yeneneh Abebe says:

    Hi I love all your webnars on PW.Is there any chance I can get the demo script for the webnar ‘Overcoming Common Tabular and Power Pivot Feature Limitations’ you did on 1/22/2015.
    Thx for all you do for the IT industry.

Leave a Reply