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.
Appologies if this has been covered but I cant find anything to show how to do this when doing a search!
I have Quarters that run in the following order:
Jan - March = Q4
April - June = Q1
July - September = Q2
October - December = Q3
I need to build a column that shows the years as follows, in order to filter the visuals by Quarter Year
Date Month Quarter Year QYear
01/01/17 1 Q4 2017 2016 - 2017
01/01/18 1 Q4 2018 2017 - 2018
01/01/19 1 Q4 2019 2018 - 2019
Any help greratfully recived
Solved! Go to Solution.
Position of * 1 is wrong
Year = ((FORMAT('Date'[Date],"YYYY") *1) -1) & "-"&FORMAT('Date'[Date],"YY")
It should be after format close )
For completeness of this thread, I solved the issues with a couple of columns for year quarter and FY quarter and then combined the year from the year coloumn to match them up in order to produce slicers.
Year Q (Jan - Dec) = Yr Q = "Q" & INT ( FORMAT ( 'DATE'[Date] , "q" ) )
and
FYQ (April - March UK) =
then used the following DAX
For Year
Year = ((FORMAT('Calendar'[Date],"YYYY") *1) -1) & "-"&FORMAT('Calendar'[Date],"YY")
@amitchandak
Thanks for the quick response, but the code whilst its on the lines I was working on (With format ), is not quite right. Could you check please
Position of * 1 is wrong
Year = ((FORMAT('Date'[Date],"YYYY") *1) -1) & "-"&FORMAT('Date'[Date],"YY")
It should be after format close )
Thanks for that, I was missing the ( ........ *1 ) -1 ) from my coding to get it to work.
To understand the logic , how does this part of the code work?
When we get YYYY from format usually it text. Just to make sure it becomes number I multiplied it with 1. But, You can also try -1 directly.
Thanks for the explination, I had an play with those numbers to see how it affected the result.
Now I just need to fix the cross over years, as currently it is not giving the corect set of Quarters the correct years.
Q1 should then change to 2017-18 not when the year changes in the year column
For completeness of this thread, I solved the issues with a couple of columns for year quarter and FY quarter and then combined the year from the year coloumn to match them up in order to produce slicers.
Year Q (Jan - Dec) = Yr Q = "Q" & INT ( FORMAT ( 'DATE'[Date] , "q" ) )
and
FYQ (April - March UK) =
then used the following DAX
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |