Forum Discussion
Create FY-Month column label based on "Date" column
Hello,
I have a date column of cloud spend. I need to present the cost based on Fiscal Year format, i.e., FY24-May-24. Here is the magic decorder ring for the label breakdown: Please note the Fiscal Year runs from July to June.
Can you please help me with how to accomplish this?
| Month | Label |
| Jul-23 | FY24-Jul-23 |
| Aug-23 | FY24-Aug-23 |
| Sep-23 | FY24-Sep-23 |
| Oct-23 | FY24-Oct-23 |
| Nov-23 | FY24-Nov-23 |
| Dec-23 | FY24-Dec-23 |
| Jan-24 | FY24-Jan-24 |
| Feb-24 | FY24-Feb-24 |
| Mar-24 | FY24-Mar-24 |
| Apr-24 | FY24-Apr-24 |
| May-24 | FY24-May-24 |
| Jun-24 | FY24-Jun-24 |
| Jul-24 | FY25-Jul-24 |
| Aug-24 | FY25-Aug-24 |
| Sep-24 | FY25-Sep-24 |
| Oct-24 | FY25-Oct-24 |
| Nov-24 | FY25-Nov-24 |
| Dec-24 | FY25-Dec-24 |
i think you need to create that column by using DAX, not in the power query.
if you want to do that in PQ， you can try this
=if List.Contains({"Jul","Aug","Sep","Oct","Nov","Dec"} ,Text.Start([Month],3)) then "FY"&Text.From(Number.From( Text.End([Month],2))+1)&"-"&[Month] else "FY"&Text.End([Month],2)&"-"&[Month]
13 Replies
- rajendraongole1
Super User
Hi anaib - create a calculated column with fiscal year label as below
FY-Month =VAR MonthNumber = MONTH([Date])VAR YearNumber = YEAR([Date])VAR FiscalYear = IF(MonthNumber >= 7, YearNumber + 1, YearNumber)VAR ShortYear = RIGHT(FiscalYear, 2)VAR MonthName = FORMAT([Date], "MMM")VAR ShortYearLabel = FORMAT([Date], "yy")RETURN "FY" & ShortYear & "-" & MonthName & "-" & ShortYearLabelHope it works
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!! Hi rajendraongole1 TYVM for the assistance. I am running into the "Token Eof expected." error. I must be doing something wrong?
ryan_mayui think you need to create that column by using DAX, not in the power query.
if you want to do that in PQ， you can try this
=if List.Contains({"Jul","Aug","Sep","Oct","Nov","Dec"} ,Text.Start([Month],3)) then "FY"&Text.From(Number.From( Text.End([Month],2))+1)&"-"&[Month] else "FY"&Text.End([Month],2)&"-"&[Month]
Thank you ryan_mayu rajendraongole1
The DAX method worked fine. However the columns are not sorted properly. Can you please help?
It should be FYxx-Mon-Yr but I see FY24-Apr-24 is listed before FY24-Feb-24 and so forth.
- Ashish_Mathur
Hi,
Share the Date and spend columns. Share data in a format that can be pasted in an MS Excel file.
- Ashish_Mathur
Hi,
In another worksheet of the same file, show the expected result.
