Forum Discussion
Custom Column on YYYYMM
Hi team,
I am new to PowerBI and working my way to figuring out the intricate functions in BI.
I have a SQL database linked to my Dashboard, and i actually need an additional column to be added into my report.
I would need to create a custom column that is able to identify which Financial Year a particular date is. Im not sure how to kick start this.
Referencing to Column "ClosedAt" data are stored in the following format YYYYMM and from here I would need to identify in which financial year this falls on and the data should be added to Custom Column "FinancialYear".
As the Financial year starts from October. E.g. FY22 = Oct 21 to Sep 22, FY23 = Oct22 to Sep 23.
Thanks in advance.
Hi noob123newbie ,
Open "Advanced Editor" and copy and paste the following code. You can check the steps on the right side.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc65DcAgEAXRXogd7P7lrAXRfxvIssyQPU00cyaZ3EZaz0c36FA/ZQ6vGjDDAitssMPzIB7Eg3gIHsKuGjDDAitssMPzEDzE+7A2", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClosedAt = _t]), #"Split Column by Position" = Table.SplitColumn(Source, "ClosedAt", Splitter.SplitTextByPositions({0, 4}, false), {"ClosedAt.1", "ClosedAt.2"}), #"Added Custom" = Table.AddColumn(#"Split Column by Position", "Custom", each Date.FromText([ClosedAt.1]&"/"&[ClosedAt.2]&"/"&"1")), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each if Date.Month([Custom])>=10 then "FY"&Text.From(Value.FromText([ClosedAt.1])+1) else if Date.Month([Custom])>=1 and Date.Month([Custom])<10 then "FY"&[ClosedAt.1] else null), #"Merged Columns" = Table.CombineColumns(#"Added Custom1",{"ClosedAt.1", "ClosedAt.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"), #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Custom"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged", "ClosedAt"}, {"Custom.1", "CustomFY"}}) in #"Renamed Columns"Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
New column =
_year = if(month([Date]) <10, Year([Date]) -1, Year([Date]) ) // Or if(month([Date]) <10, Year([Date]) , Year([Date])+1 )
_month = if(month([Date]) <10,month([Date])+3, month([Date]) -9 ) //
return
(_year*100 )+ _month
refer more options and columns
Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5ee47de6a208
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1
DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s
Power BI Date Table: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=18180sI am getting an error below when i input the formula.
noob123newbie , You are doing it in power query
Try like
(if Date.Month([Date]) < 10 then Date.Year([Date]) - 1 else Date.Year([Date]) )*100 + (if Date.Month([Date]) < 10 then Date.Month([Date]) + 3 else Date.Month([Date]) - 9)
https://medium.com/@amitchandak/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1
Hi noob123newbie ,
Open "Advanced Editor" and copy and paste the following code. You can check the steps on the right side.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc65DcAgEAXRXogd7P7lrAXRfxvIssyQPU00cyaZ3EZaz0c36FA/ZQ6vGjDDAitssMPzIB7Eg3gIHsKuGjDDAitssMPzEDzE+7A2", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClosedAt = _t]), #"Split Column by Position" = Table.SplitColumn(Source, "ClosedAt", Splitter.SplitTextByPositions({0, 4}, false), {"ClosedAt.1", "ClosedAt.2"}), #"Added Custom" = Table.AddColumn(#"Split Column by Position", "Custom", each Date.FromText([ClosedAt.1]&"/"&[ClosedAt.2]&"/"&"1")), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each if Date.Month([Custom])>=10 then "FY"&Text.From(Value.FromText([ClosedAt.1])+1) else if Date.Month([Custom])>=1 and Date.Month([Custom])<10 then "FY"&[ClosedAt.1] else null), #"Merged Columns" = Table.CombineColumns(#"Added Custom1",{"ClosedAt.1", "ClosedAt.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"), #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Custom"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged", "ClosedAt"}, {"Custom.1", "CustomFY"}}) in #"Renamed Columns"Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
