Forum Discussion
Date string list - ascending order
Very novice here. Be gentle 🙂 Thanks
I have a number of dates that are already sorted in column being referenced but when concat in new string they aren't given in Ascending order.
Certs string = CONCATENATEX(FILTER((Site),Site[Site No]=EARLIER(Site[Site No])),Site[Effective Date],"; ")
it won't accept ,,ASC / ,ASC which I used before in RANKX but that time it wasn't using any extra condition
So the 2009 and 2011 I'd like at the start, are being presented at the end of the string. The string is fine from 2013 but just wondering on possibilities
Hi JK-1 ,
You should be able to use the ORDER BY optional parameter in the concatenatex function. This should work:CONCATENATEX(FILTER(Site, Site[Site No] = EARLIER(Site[Site No])), [Effective Date], ";", [Effective Date], ASC)
Notice the second "Effective Date" field. We're using it to tell the expression to sort by the effective date in ascending order.
6 Replies
Hi JK-1 ,
You should be able to use the ORDER BY optional parameter in the concatenatex function. This should work:CONCATENATEX(FILTER(Site, Site[Site No] = EARLIER(Site[Site No])), [Effective Date], ";", [Effective Date], ASC)
Notice the second "Effective Date" field. We're using it to tell the expression to sort by the effective date in ascending order.Would there be a way to expand the expression so in the sorted date string it returns dates up to a certain point in time, either TODAY() - or using another column containing alternative end date(s)?
🤞
Hi JK-1
Yes, you would add it within your FILTER criteria:
CONCATENATEX( FILTER('Site', 'Site'[Site No] = EARLIER('Site'[Site No]) && // Using the double && or || to denote that there are additional conditions to be met [Effective Date] >= DATE(2025, 1, 1) // && means 'and' while || means 'or' // continue with your filter criteria until it meets your requirements. Sample. ), [Effective Date], "; ", [Effective Date], ASC )
