Create FY-Month column label based on "Date" column

[anaib]

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

[Ashish_Mathur]

Hi,

Share the Date and spend columns.  Share data in a format that can be pasted in an MS Excel file.

[anaib]

Hi @Ashish_Mathur  here is the sample dataset.

Sampledata.xlsx

[Ashish_Mathur]

The file does not open - there is no download option

[Ashish_Mathur]

Hi,

In another worksheet of the same file, show the expected result.

[anaib]

Added a "Suggestion1" worksheet.  Kindly take a look.

[anaib]

Hi @rajendraongole1 TYVM for the assistance.  I am running into the "Token Eof expected." error.  I must be doing something wrong?

[ryan_mayu · Accepted solution]

@anaib 

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]

[anaib]

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.

[ryan_mayu]

@anaib 

you can try to create a sort column

sort =

var _month=SWITCH(mid('Table'[Label],6,3),"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)

return "20"&mid('Table'[Label],3,2)*100+_month

and select lable column then sort by sort column

[anaib]

@ryan_mayu tyvm for the suggestion.  I am getting this error when sorting.  My sort column is called "FY-Sort", my label column is called "FY-Month" (pls note, this is also a calculated column).

[ryan_mayu]

then you modify the data from your original column

sort =

var _month=SWITCH(left('Table'[Month],3),"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)

return "20"&right('Table'[Month],2)*100+_month

and set the sort column data type to whole number

at last sort the month column by sort column

[anaib]

[rajendraongole1]

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 & "-" & ShortYearLabel

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!