Month into quarter

[Migscruz]

Hello,

I have a database that has a date column like this:

01/03/2021 --> Values with this date refers to First Quarter of the year.

01/06/2021 --> Values with this date refers to Second Quarter of the year.

01/09/2021 --> Values with this date refers to Third Quarter of the year.

01/12/2021 --> Values with this date refers to Fourth Quarter of the year

How can i transform those dates into a quarter? Because when i do a graph with X axis it shows like 01/03/2021 and actually the values refers to the first quarter.

Thanks!

[rsbin · Accepted solution]

@Migscruz,

One way to do this is to create a new Calculated Column using the SWITCH function:

Quarter = SWITCH(
             TRUE(),
             [YourDateColumn] = 01/03/2021, "Q1",
             [YourDateColumn] = 01/06/2021, "Q2",
             [YourDateColumn] = 01/09/2021, "Q3", 
             "Q4" )

This is similar to a nested IF function in Excel, but I find it much cleaner this way.

if you will have multiple years in your data, you will probably need a Year column as well:

Year = Year ( [YourDateColumn] )

And then lastly concatentate these two columns together.  Something like Year-Quarter:

Year-Quarter = [Year] & "-" & [Quarter].

Hope this helps.

[ryan_mayu · Accepted solution]

@Migscruz 

maybe you can try this

fiscalmonth = 
VAR _MONTH=MONTH('Calendar'[Date])
RETURN IF(_MONTH>2,_MONTH-2,_MONTH+10)

fiscalquarter = ROUNDUP('Calendar'[fiscalmonth]/3,0)

[rsbin · Accepted solution]

@Migscruz,

One way to do this is to create a new Calculated Column using the SWITCH function:

Quarter = SWITCH(
             TRUE(),
             [YourDateColumn] = 01/03/2021, "Q1",
             [YourDateColumn] = 01/06/2021, "Q2",
             [YourDateColumn] = 01/09/2021, "Q3", 
             "Q4" )

This is similar to a nested IF function in Excel, but I find it much cleaner this way.

if you will have multiple years in your data, you will probably need a Year column as well:

Year = Year ( [YourDateColumn] )

And then lastly concatentate these two columns together.  Something like Year-Quarter:

Year-Quarter = [Year] & "-" & [Quarter].

Hope this helps.