Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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.
Sure, you can use SWITCH(TRUE()...) like this:
Column =
SWITCH(TRUE(),
[Date] >= DATE(2019,11,16) && [Date] <= DATE(2020,2,15),2020,
...
BLANK()
)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |