Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
This is my first post on here so if I am not posting in the right spot splease direct me. I am use to writing SQL queries but my current job does not have a data base structure so I have been working in Power BI alot. I am trying to figure out how to make a case statement such as
"CASE
WHEN StartDate between '7/1/18' and '9/21/18' THEN 'FY19-Q1'
WHEN StartDate between '9/24/18' and '12/21/18' THEN 'FY19-Q2'
WHEN StartDate between '12/31/18' and '3/22/19' THEN 'FY19-Q3'
ELSE 'Find QTR'
END;"
-I have tried things similar to
"Test QTR =
Switch ( True()
'Tablename'[Start date] >=7/1/18, "FY19-Q1",
'Tablename'[Start date] >=9/24/18,"FY19-Q2"
)"
But for some reason it is saying it is saying my start date column does not exist and I get the nice red squiggly. Would really appreciate some help!
Hi. I don't think you can use between in switch. It only asks for one value. You should get if from if from if. Like this:
IF(
AND( Table[StartDate] > DATE(1,7,2018) , Table[StartDate] < DATE(21,9,2018) ),
"FY19-Q1",
IF(
AND( Table[StartDate] > DATE(24,9,2018) , Table[StartDate] < DATE(21,12,2018) ),
"FY19-Q2",
IF(
AND( Table[StartDate] > DATE(31,12,2018) , Table[StartDate] < DATE(22,3,2018) ),
"FY19-Q3",
"Find QTR"
)
)
)
You should also try creating a Calendar Fiscal Date Table. It's like a regular calendar table but with the months and quarters of your business configured.
Hope this helps,
Happy to help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |