topic Re: How to combine Year and quarter columns in one column on a table? in DAX Commands and Tips

How to combine Year and quarter columns in one column on a table?

Anonymous — Fri, 21 Feb 2020 12:26:28 GMT

Hi,

I have a table with 4 columns (Variable, year, quarter and value). I want to combine the year and quarter columns in one column.

Please find below the table.

Thanks for your time in advance

Q007

Variable Year Quarter Value

Variable	Year	Quarter	Value
FWA	2010	Q1	53627056
FWA	2010	Q2	58081119
FWA	2010	Q3	63781289
FWA	2010	Q4	70041487
FWA	2011	Q1	78019812
FWA	2011	Q2	85717968
FWA	2011	Q3	92185934
FWA	2011	Q4	99275469
FWA	2012	Q1	108896889
FWA	2012	Q2	118959095
FWA	2012	Q3	127472408
FWA	2012	Q4	136606883
FWA	2013	Q1	148416477
FWA	2013	Q2	160697670
FWA	2013	Q3	171694528
FWA	2013	Q4	182625385
FWA	2014	Q1	194796221
FWA	2014	Q2	206818234
FWA	2014	Q3	219126737
FWA	2014	Q4	231551080
FWA	2015	Q1	245469495
FWA	2015	Q2	260948600
FWA	2015	Q3	278874269
FWA	2015	Q4	298899900
FWA	2016	Q1	327538953
FWA	2016	Q2	356875850
FWA	2016	Q3	392291621
FWA	2016	Q4	427414578
FWA	2017	Q1	475771846
FWA	2017	Q2	524530295
FWA	2017	Q3	579005581
FWA	2017	Q4	633132713
FWA	2018	Q1	728597556
FWA	2018	Q2	826538209
FWA	2018	Q3	934161410
FWA	2018	Q4	1042654201
FWA	2019	Q1	1108913292
FWA	2019	Q2	1219564570
FWA	2019	Q3	1262668593
FWA	2019	Q4	1306566235

Re: How to combine Year and quarter columns in one column on a table?

Anonymous — Fri, 21 Feb 2020 13:04:06 GMT

is it ok for u ?

Re: How to combine Year and quarter columns in one column on a table?

AlexAlberga727 — Fri, 21 Feb 2020 15:34:30 GMT

Year/Qtr = dDateTable[Year] & "-" & dDateTable[Quarter]

Re: How to combine Year and quarter columns in one column on a table?

Anonymous — Fri, 21 Feb 2020 16:17:53 GMT

Thanks a lot,

It was exactly what I wanted

Re: How to combine Year and quarter columns in one column on a table?

Anonymous — Fri, 21 Feb 2020 16:53:51 GMT

Hi again,

a follow-up question. I am trying to change the "Type" of Year/Qtr from Text to Date but it won't work. Any idea?

Year/Qtr = dDateTable[Year] & "-" & dDateTable[Quarter]

Re: How to combine Year and quarter columns in one column on a table?

AlexAlberga727 — Fri, 21 Feb 2020 16:58:54 GMT

Beacuse these values were concatenated they're considered TEXT. To mark a field as "Date" the values must be consistant with a date format (ex. 01/01/2020, 01-01-2020, 20/1/20). This field will not be able to be moved to a DATE type.

My suggestion is - retain date information within the table, and insert a slicer with the Year/Qtr values. This way you, or your users, will be able to quickly filter the data by that criteria.

Re: How to combine Year and quarter columns in one column on a table?

AlexAlberga727 — Fri, 21 Feb 2020 17:03:38 GMT

Re: How to combine Year and quarter columns in one column on a table?

Anonymous — Fri, 21 Feb 2020 17:18:43 GMT

Thanks for explanation and solution!

Is it doable to convert the "Quarter" column to date format? the conversion of the year column was straight forward.

If yes, would I be able to achieve what I originally (combining Quarter and Year columns to make a new "Year/Qtr" column in date format) wanted?

My understanding of DAX is very limited.

Re: How to combine Year and quarter columns in one column on a table?

AlexAlberga727 — Fri, 21 Feb 2020 17:22:30 GMT

A text value cannot be formatted as a date.

However, in what ever table you're looking at im sure contains a date. You have calculated the Year/QTR for each record, which already contains date information.

Because you have now created a Year/QTR field - this field has essentially categorized your data (and dates), by year and quarter. You can now filter/sort by this field if needed opposed to the date field.

Tell me what your ultimate goal is for this field being formatted as a date and I'll point you in a better direction.

Re: How to combine Year and quarter columns in one column on a table?

Anonymous — Mon, 24 Feb 2020 10:01:14 GMT

Hi,

My ultimate goal is to be able to have a slicer for time/date on my reports which are made of a set of variables tables. I have a time intelligence table, and I'd like to be able to connect it to the date (Year, QRT) column of our tables. As you know, currently I have two columns in ant given table, one column for year and another column for quarters.

Best

Re: How to combine Year and quarter columns in one column on a table?

m_g1983 — Mon, 07 Jun 2021 05:51:30 GMT

I want to retrieve the previous quarter values by using this function

CALCULATE(SUM('Appended Tables'[Total BWS]), PREVIOUSQUARTER(FIRSTDATE('Appended Tables'[Date])))

Answer comes correct.

The problem is in visualization. I want to display through this key op2-2021-q2 but can't display the value as it is text and the input we need is date.

please help in this.

Re: How to combine Year and quarter columns in one column on a table?

AlexAlberga727 — Mon, 07 Jun 2021 12:16:59 GMT

This wont work because the FX provided is concatenating values containing text. You may want to utilze your actual date field when creating the visualization you're wanting. Then also include the Year/Qtr values as either a filter, or simply additional information - to indicate when it happened.

If you're passing a text value through a function that requires a date - you will have to obviously correct that. The FX we created should be a relationship to your date field. So any date provided will have a relational year/qtr info and the same goes for the new calculation you're putting together now. Hope that makes sence. Let me know if there is any more infomration you could provide to help me assist more.

Enjoy!

Re: How to combine Year and quarter columns in one column on a table?

m_g1983 — Fri, 11 Jun 2021 07:11:49 GMT

HI,

I am using PreviousQuarter Fx to retrieve the previous Quarter values as below:

CALCULATE(SUM('Appended Tables'[Total BWS]), PREVIOUSQUARTER(FIRSTDATE('Appended Tables'[Date])))

The problem is that it needs date.

i want to pass as text.

i want some other function wherein i coulld fetch the previous quarter values without using previousquarter fx?

please help

Re: How to combine Year and quarter columns in one column on a table?

theov — Mon, 21 Mar 2022 12:26:31 GMT

You can use combination of YEAR and QUARTER functions and joind them using CONCATENATE or "&" sign. Don't forget to join "Q" just before the quarter function also 😉 cause QUARTER only gives the number of quarter.

Here is a good video explaining it too:

https://www.youtube.com/watch?v=Oq5WOmo94_Q