Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hello @FlankyPank2___ ,
refer to the following https://youtu.be/Lt_nNLQz0IE?si=F0ESOg7NlLI6bPWM
Proud to be a Super User! | |
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
Hello @FlankyPank2___ ,
refer to the following https://youtu.be/Lt_nNLQz0IE?si=F0ESOg7NlLI6bPWM
Proud to be a Super User! | |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
95 | |
80 | |
77 | |
66 |
User | Count |
---|---|
130 | |
106 | |
105 | |
86 | |
72 |