Wildcard For Year in Conditional Column

[Anonymous]

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!

[v-lili6-msft · Accepted 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

[Anonymous]

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.