Hi Team,
I have Date field that contains 01-01-2014 to till now. I need to show below image.
Here 2019 data will come it automatically changes to 2017 & Prior, 2018(Q1,Q2,Q3,Q4),2019(Q1,...)
Thanks Much,
Veeru.
Jun 12, 2018 4:16 AM
Hi Veeru,
Use this:
if YEAR([DATE])<2017
then '2016 and Prior'
else str( YEAR([DATE])) + ' Q' + str( DATEPART('quarter', [DATE]))
END
Please see attached example.
Thanks,
Mavis
Jun 12, 2018 4:32 AM
Hi, Veeru
Please find my solution attached as well as below screenshot
Hope this helps
ZZ
Hi Veeru,
max year :
{ FIXED : DATEPART('year' ,max([Order Date]))}
new date :
if DATEDIFF('year', [Order Date] , MAKEDATE([current year],1,1) )>=2
then MAKEDATE([current year]-2,1,1)
ELSE [Order Date]
end
Hi Mavis,
Thanks for your reply. Here one small change i done because of 2019 data will come it will not automatically changes 2017 & Prior.
IF YEAR([NEW ORDER DATE]) <= YEAR(DATEADD('year',-2,TODAY()))
THEN STR(YEAR(DATEADD('year',-2,TODAY())))+'and Prior'
ELSE
str( YEAR([NEW ORDER DATE])) + ' Q' + str( DATEPART('quarter', [NEW ORDER DATE]))
END