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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jamming_Mon
Frequent Visitor

Creating New Column in Query for Fiscal Year and Fiscal Quarter

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? 

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

Hi, @Jamming_Mon 

 

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

b1.png

 

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:

b2.png

 

Best Regards

Allan

 

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

2 REPLIES 2
v-alq-msft
Community Support
Community Support

Hi, @Jamming_Mon 

 

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

b1.png

 

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:

b2.png

 

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
Super User
Super User

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

 

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.