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
Anonymous
Not applicable

Change Date field to quarter in Power BI

Hi All,

 

I have a date field called [Event date] that I want the output to be converted to QuarterEventDate.

For example 2017-01-01 to read as 2017Q1. Please How do I achieve it?  Unfortunately, I have tried a method I saw here but it's not working for me.

 

Thanks.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thanks Li  ,

A quick check shows that I cannot use FORMAT  in a directquery models and I do not use Import models.  I'll accept it as a solution whilst taking time to go through  them.

View solution in original post

5 REPLIES 5
Gordonlilj
Solution Sage
Solution Sage

Hi,

 

You could create a calculated column with this code

CalcColumn = Table[DateColumn].[Year] & "Q" & Table[DateColumn].[QuarterNo]

 

This works as well

CalcColumn = YEAR(Table[DateColumn]) & "Q" & ROUNDUP(MONTH(Table[DateColumn])/3, 0)

 

You could also create it in power query by creating a custom column with something like this

Number.ToText(Date.Year([DateColumn])) & "Q" & Number.ToText(Date.QuarterOfYear([DateColumn]))

 

Anonymous
Not applicable

I actually tried the first one but I dont know why it's not working for me. Anyway, I 'll come back to it, I had to do it in SQL before loading to Power BI using:

CAST(DATEPART(YEAR,[Event Date]) As varchar(4)) + 'Q'+ CAST(DATEPART(Quarter,[Event Date]) as varchar) as Quarter

hi, @Anonymous 

You could use these two formula to create two calculate column to get it:

quarter1 = YEAR ( 'Date'[Event Date] )&"Q" & FORMAT ( 'Date'[Event Date], "q")
quarter2 = YEAR('Date'[Event Date]) & "Q" & ROUNDUP(MONTH('Date'[Event Date])/3, 0)

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns

Result:

1.JPG

By the way, here are some frequently-used dax formula to create different date format column for you refer to:

2.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks Li  ,

A quick check shows that I cannot use FORMAT  in a directquery models and I do not use Import models.  I'll accept it as a solution whilst taking time to go through  them.

calerof
Impactful Individual
Impactful Individual

Hi @Anonymous ,

 

Make sure you have a date table.

 

Regards,

 

Fernando

 

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.