Forum Discussion

Migscruz's avatar
Migscruz
Icon for Helper I rankHelper I
4 years ago
Solved

Month into quarter

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!

Need Help
  • rsbin's avatar
    rsbin
    4 years ago

    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's avatar
    ryan_mayu
    4 years ago

    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)

2 Replies

  • rsbin's avatar
    rsbin
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    4 years ago

    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)