Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
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]))
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:
By the way, here are some frequently-used dax formula to create different date format column for you refer to:
Best Regards,
Lin
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.
Hi @Anonymous ,
Make sure you have a date table.
Regards,
Fernando
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |