Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Migscruz
Helper I
Helper I

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!

2 ACCEPTED SOLUTIONS
rsbin
Super User
Super User

@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.

View solution in original post

ryan_mayu
Super User
Super User

@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)

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ryan_mayu
Super User
Super User

@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)

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




rsbin
Super User
Super User

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.