locked
Creating a fiscal date table that includes Day of Year and Week of Year column RRS feed

  • Question

  • Hi,

    Our fiscal year runs between 1st October through to 30th September and I'm trying to create a date table that I can use to represent this in Powerpivot (I'm using Excel 2013). I had no probably creating columns for Fiscal month or fiscal year, however I am having issue breaking it down more than that, specifically I cannot work out how to calculate:

    - day of the year
    - week of the year (with week one starting on the first day of the year rather than the first Monday)

    I worked out how to do the day of the year until I had to try and account for leap years which broke the formula I had! Can anyone please recommend solutions, I cannot see anything online about this. Obviously I know that it is simple to do this for a normal calendar year but this approach doesn't work with a custom fiscal year.

    Whilst it is useful for several reasons, my main reason for wanting week is so that I can plot my Pivotcharts more smoothly (although I may have missed an easier way to do that).

    Thanks,

    James

    Tuesday, April 8, 2014 7:54 PM

Answers

  • For my example I started with 1 column in Power Pivot called [Date]

    I created the following Columns

    [DayofMonth] =Day([Date])

    [MonthNumber] =Month([Date])

    [Fiscal Year] =IF([MonthNumber]< 10, Year([Date]), (Year([Date])+1))

    [Fiscal Day] =[Date] - DATE(([Fiscal Year] -1), 10,1) +1

    [Fiscal Week] =RoundUp([Fiscal Day] / 7, 0)

    Change [Date] to whatever your date column is and those DAX formulas will get you what you need

    • Marked as answer by Maracles Wednesday, April 9, 2014 1:11 PM
    Wednesday, April 9, 2014 12:30 PM

All replies

  • I should also mention that if anyone can recommend a date table that I can download and customise with my own fiscal year this would also work!
    Tuesday, April 8, 2014 7:55 PM
  • For my example I started with 1 column in Power Pivot called [Date]

    I created the following Columns

    [DayofMonth] =Day([Date])

    [MonthNumber] =Month([Date])

    [Fiscal Year] =IF([MonthNumber]< 10, Year([Date]), (Year([Date])+1))

    [Fiscal Day] =[Date] - DATE(([Fiscal Year] -1), 10,1) +1

    [Fiscal Week] =RoundUp([Fiscal Day] / 7, 0)

    Change [Date] to whatever your date column is and those DAX formulas will get you what you need

    • Marked as answer by Maracles Wednesday, April 9, 2014 1:11 PM
    Wednesday, April 9, 2014 12:30 PM
  • That worked wonders, thanks very much for the help.
    Wednesday, April 9, 2014 1:10 PM
  • I know this one was updated and solved for me a while ago, however I need an extension of it and thought it better to add than create a new post.

    I need a modification of [Fiscal Week] which is [Fiscal Week - Monday Start] i.e. the fiscal week based on each week starting on Monday. 

    I have successfully down this for Calendar week using WEEKNUM(Cal Date, 2) but cannot figure out how to translate this for Fiscal Week. 

    For my Fiscal Week column I am using the solution above:

    [Fiscal Week] =RoundUp([Fiscal Day] / 7, 0)

    Thanks for any replies.
    Wednesday, January 6, 2016 1:04 AM