Forum Discussion
Help with Fiscal Year
Hi all you clever people!
I'm stuck. I created a calendar year table so as I can plot daily occupancy on a line graph. This all works like a treat but I'd really love it to be in fiscal year. Is there any way I can amend the data below to generate fiscal year and the relevent quarters? I've done lots of reading but I just can't bend my brain around this one.
Code I used:
1 Date23-24 = ADDCOLUMNS(CALENDAR(Date(2023,04,01), DATE(2024,03,31)),"Month no", MONTH([Date])
2 , "Year", YEAR([Date])
3 , "Month Year", format([Date], "mmm-yyyy")
4 , "Month year sort", YEAR([Date])*100+MONTH([Date])
5 , "Qtr Year", format({Date}, "yyyy-\QQ2)
6 , "Qtr", QUARTER([Date])
7 , "Month", FORMAT([Date],"mmmm")
8 , "Month sort", month([Date]))
This gives me columns that look like this:
| Date | Month no | Year | Month Year | Month Year sort | Qtr Year | Qtr | Month | Month Sort |
| 01/07/2023 | 7 | 2023 | Jul-2023 | 202307 | 2023-Q3 | 3 | July | 7 |
| 02/07/2023 | 7 | 2023 | Jul-2023 | 202307 | 2023-Q3 | 3 | July | 7 |
Is there anyway I can convert this to fiscal?
Thanks so much for your time.
5 Replies
Thank you! This you tube tutorial was just what I needed and couldn't seem to find myself. Thank you
A few typo's in that message "yyyy-\QQ" not "yyyy-\QQ2
Year Qtr column should read 2023-Q3 not 2023-Q33
Sorry for any confusion
Use an external reference table (for example and Excel file on a SharePoint) for your fiscal calendar, especially if you plan to add fiscal weeks.
Thank you for your help - I opted for the you tube posted below
