Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Wildcard For Year in Conditional Column

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 cr...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

     

     

     

     

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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.