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-20

Q2

May-21

Aug-20

Q3

Aug-21

Nov-20

Q4

Nov-21

Feb-20

Need Help

4 Replies

  • 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])