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.
I'm trying to create custom quarters for my orginization. Essentially, we want to identify anything before March 16 as Q1, June 16 as Q2, October 16 as Q3, and December 31 as Q4.
When I go to create conditional columns, I try to enter the date as #date(yyyy, mm, dd). I can't figure out what wildcard charcter or expression to use to where it's independent of year.
I want every date before March 16 to be identified as Q1 regardless of year, etc.
Thanks!
Solved! Go to Solution.
hi, @Anonymous
Do you want to do these in Edit Queries?
If so, try this way as below:
Step1:
Add a custom column
=Date.Month([Date])*100+Date.Day([Date])
Step2:
Then use this custom column to add a custom quarters column
You could also use this dax formula to add a calculate column
Result = var TD=MONTH('Date'[Date])*100+DAY('Date'[Date]) return IF(TD<=316,"Q1",IF(TD<=616,"Q2",IF(TD<=1016,"Q3",IF(TD<=1231,"Q4"))))
Best Regards,
Lin
hi, @Anonymous
Do you want to do these in Edit Queries?
If so, try this way as below:
Step1:
Add a custom column
=Date.Month([Date])*100+Date.Day([Date])
Step2:
Then use this custom column to add a custom quarters column
You could also use this dax formula to add a calculate column
Result = var TD=MONTH('Date'[Date])*100+DAY('Date'[Date]) return IF(TD<=316,"Q1",IF(TD<=616,"Q2",IF(TD<=1016,"Q3",IF(TD<=1231,"Q4"))))
Best Regards,
Lin
Very creative solutions. Both work.
I also used the date functionality to create a new "Day of Year" column. I figured out which day of year each custom date I was trying to calculate and used that to create conditional column. Only issue with this one is leap years adjusts the date by one day.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |