Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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:
Year/Qtr = dDateTable[Year] & "-" & dDateTable[Quarter]
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]
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!
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.
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.
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.
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.
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
Q
Thanks a lot,
It was exactly what I wanted
is it ok for u ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |