Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
noob123newbie
Frequent Visitor

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. 

 

Screenshot 2023-11-01 at 11.47.00 AM.png

 

 

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

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"

vtangjiemsft_0-1698997999727.png

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. 

 

View solution in original post

5 REPLIES 5
v-tangjie-msft
Community Support
Community Support

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"

vtangjiemsft_0-1698997999727.png

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. 

 

amitchandak
Super User
Super User

@noob123newbie ,

 

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-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
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=18180s

I am getting an error below when i input the formula. 

 

Screenshot 2023-11-01 at 12.50.51 PM.png

@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,

 

Not sure why upon running the query, its reflecting the FY incorrectly. 

E.g. Date : 202308 - showing as 202208 instead where it should be in the FY23 financial year.

 

 

Screenshot 2023-11-01 at 2.56.57 PM.png

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.