Forum Discussion

Mooihoek's avatar
Mooihoek
Icon for Helper II rankHelper II
8 years ago

Translate Dates into Custom Financial Quarters

I have a simple date column in a table in the format below:   10/10/2018 01/02/2017 04/02/2018   How can I arrange these by some custom financial quarter dates?       Q1 Feb-20 May...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Create a calculated column and try to use the following DAX:

 

Q = "Q"&ROUNDUP(MONTH(IF('US Calendar'[Month]<=7,'US Calendar'[Date]-50,'US Calendar'[Date]-51))/3,0)

Mooihoek's avatar
Mooihoek
Icon for Helper II rankHelper II
8 years ago

Doesnt seem to work, I get :

 

DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Did you use the following DAX to change get the number of Month?

     

    Month = MONTH('US Calendar'[Date])

     

    • Mooihoek's avatar
      Mooihoek
      Icon for Helper II rankHelper II
      8 years ago

      thats it!  How does the logic work there?  if I wanted to amend to change the dates to:

       

      Q1

      Feb-04

      May-05

      Q2

      May-06

      Aug-04

      Q3

      Aug-05

      Nov-03

      Q4

      Nov-04

      Feb-03