Date string list - ascending order

[JK-1]

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

[hnguy71 · Accepted solution]

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.

[hnguy71 · Accepted solution]

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.

[JK-1]

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)?

🤞

[hnguy71]

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
)

[JK-1]

Thanks for this. It returns the dates as just TRUE, TRUE, FALSE

Etc.

rather than just the date format previously. Was hoping there might be a way to only display the trues but with their dates intact - and for the false ones to drop out.

sorry for the overly complicated ask

[hnguy71 · Accepted solution]

Hi @JK-1 

You seem to be missing a part of the evaluation. I've attached a sample for your reference.

[JK-1]

Thank you for your help. Much appreciated 👍 for time and extra effort