Creating New Column in Query for Fiscal Year and Fiscal Quarter

[Jamming_Mon]

Say for Example, Q1 2020 is from 11/16/2019 - 02/14/2020, Q2 from 02/15/2020 - 05/16/2020 and so forth.  Is there a formula I can use in Query to say something like "If Dates are between 11/16/2019 - 02/14/2020, then FY = 2020 and FQ = Q1" and so forth? 

[v-alq-msft · Accepted solution]

Hi, @Jamming_Mon 

Based on your description, I created data to reproduce your scenario.

Then you may add a column in Power Query as follows.

= Table.AddColumn(#"Changed Type", "Custom", each if [Date] >= #date(2019, 11, 16)and[Date] <= #date(2020, 2, 14) then "FY2020 FQ1" else if [Date] >= #date(2020, 2, 15)and[Date] <= #date(2020, 5, 16) then "FY2020 FQ2" else if [Date] >= #date(2020, 5, 17)and[Date] <= #date(2020, 8, 14) then "FY2020 FQ3" else if [Date] >= #date(2020, 8, 15)and[Date] <= #date(2020, 11, 15) then "FY2020 FQ4" else null)

Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUNzTTNzIwtFSK1YFwzVG5Fgiukb6hCZBnZADjmaLwzBA8U3SeOQrPAsGzQDHTAsVMkPUwbiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Date] >= #date(2019, 11, 16)and[Date] <= #date(2020, 2, 14) then "FY2020 FQ1" else if [Date] >= #date(2020, 2, 15)and[Date] <= #date(2020, 5, 16) then "FY2020 FQ2" else if [Date] >= #date(2020, 5, 17)and[Date] <= #date(2020, 8, 14) then "FY2020 FQ3" else if [Date] >= #date(2020, 8, 15)and[Date] <= #date(2020, 11, 15) then "FY2020 FQ4" else null)
in
    #"Added Conditional Column"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Sure, you can use SWITCH(TRUE()...) like this:

Column = 
  SWITCH(TRUE(),
    [Date] >= DATE(2019,11,16) && [Date] <= DATE(2020,2,15),2020,
    ...
    BLANK()
  )