# Creating a fiscal date table that includes Day of Year and Week of Year column

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

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