Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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