DAX, adding quarter to my due date

[CesarAustin]

Hello everyone… I’m a newbie to DAX and I’m trying to add a quarter column to my due dates…

This is how my quarters look like by month... 

Q1	Feb
	Mar
	Apr
Q2	May
	Jun
	Jul
Q3	Aug
	Sep
	Oct
Q4	Nov
	Dec
	Jan

this is my DAX 

Due Fiscal Quarter =

'Due Date'[Due Fiscal Year] & " Q"

    & IF(

        MONTH('Due Date'[Due Date]) <= 4,

        1,

        IF(

            MONTH('Due Date'[Due Date]) <= 7,

            2,

            IF(

                MONTH('Due Date'[Due Date]) <= 10,

                3,

                4                  

            )

        )

    )

this works fine except for Q4…  Q4 is Nov, Dec, and Jan…  and my calculation is putting Q1 in Jan… Nov and Dec are good.. I know it’s because of my 1st statement  “Month(due date’[due date]) <=4 1” but I don’t know how to correct it…  please help..    

Cesar

  

[CesarAustin]

thanks guys... this was very helpfull....

Cesar   

[lbendlin]

=FORMAT(EDATE([Due Date],2),"\F\Yyyyy \Qq")

[AlexisOlson · Accepted solution]

I would think you'd want EDate ( [Due Date], -1 ) since January is in FQ4.

Alternatively, this can be done in a more brute force way with a SWITCH. For example,

Due Fiscal Quarter =
    'Due Date'[Due Date]
        & SWITCH (
              MONTH ( 'Due Date'[Due Date] ),
               2, " Q1",
               3, " Q1",
               4, " Q1",
               5, " Q2",
               6, " Q2",
               7, " Q2",
               8, " Q3",
               9, " Q3",
              10, " Q3",
              11, " Q4",
              12, " Q4",
               1, " Q4"
          )

or

Due Fiscal Quarter =
VAR _FY    = 'Due Date'[Due Date]
VAR _Month = MONTH ( 'Due Date'[Due Date] )
VAR _FQ =
    SWITCH (
        TRUE (),
        _Month IN {  2,  3,  4 }, 1,
        _Month IN {  5,  6,  7 }, 2,
        _Month IN {  8,  9, 10 }, 3,
        _Month IN { 11, 12,  1 }, 4
    )
VAR _Result = _FY & " Q" & _FQ
RETURN
    _Result

These are less code efficient but might be easier to future maintainers to understand.