Resolved: CONVERT FY format to exact date in Power BI

Question:

I have a string formatted date field which says “FY23Q1”, now assuming the fiscal year starts in October and ends in September, I want to recode the string into 10/01/2022. ie the first date of the month
INPUT Expected output: FY23Q1 10/01/2022 FY23Q2 01/01/2023 FY23Q3 04/01/2023

Answer:

Add a new custom column in PQ and paste in the following code. Make sure [Column1] refers to your column with FY23Q1 date in it.

let 
year = Number.From(Text.Middle([Column1], 2,2)),
quarter = Number.From(Text.Middle([Column1], 5,6)),
startDate = Date.AddQuarters( #date(year-1+2000, 10, 1), quarter - 1)


in startDate

Full working example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcos0Mg40VIrVgTKNEExjBNMExjQBqY0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let 
year = Number.From(Text.Middle([Column1], 2,2)),
quarter = Number.From(Text.Middle([Column1], 5,6)),
startDate = Date.AddQuarters( #date(year-1+2000, 10, 1), quarter - 1)


in startDate)
in
    #"Added Custom"

If you have better answer, please add a comment about this, thank you!

Resolved: CONVERT FY format to exact date in Power BI

Resolved: How can I write CSS selector(s) that apply to table rows for all td elements on that row after a td with a certain class?

Resolved: How do I use SetWindowText with Unicode in Win32 using PowerShell?

Resolved: Shopware 400 Status Error “This value is too long. It should have 255 character or less.” When I Try Updating Database Table

Leave A Reply