How to change the Year to have the start month as October

[antigone]

Hello. I am trying to create a formula in my date table to show that, for example the year 2013 the date starts at Oct 1 and ends on Sept 31. Can anyone help me with this problem?

 

[Oeldere]

I don't think so, because there is no date Sept 31.

Without kidding, show us the file (or format of your data).

 

[antigone]

I don't think so, because there is no date Sept 31.

Without kidding, show us the file (or format of your data).

lol, good point. Actually I just figured it out I think: I used =Year(STARTOFYEAR('Date Table'[date],"09/30")). This worked perfectly. But then I also wanted to have a new month column that had October as Month 1 and September as Month 12. This was more tricky and after some searching and trying formulas, I believe that it cannot be done.

 

[Jacob Barnett]

It's good practice to use a separate calendar table in your models but in this case it's basically obligatory!! There's a bunch of good resources at PowerPivotPro under the time intellgience tag: Time Intelligence « PowerPivotPro

Long story short, this calendar table has one row per date and is related to your fact table. The calendar table can contain as many columns as you like, one of which can be 'Fiscal Year'.

 

[antigone]

It's good practice to use a separate calendar table in your models but in this case it's basically obligatory!! There's a bunch of good resources at PowerPivotPro under the time intellgience tag: Time Intelligence « PowerPivotPro

Long story short, this calendar table has one row per date and is related to your fact table. The calendar table can contain as many columns as you like, one of which can be 'Fiscal Year'.

Yes this is being done in my date table/calendar table and I added Flu Year as the new column.