Sorting Column with different date formats

[Anonymous]

Hello all,
I would like to sort a column containing different dates in different formats e.g. (Sep-2022, Oct-2022, Q-4-2022, Q-1-2023, Y-2023, Y-2023). Can someone please help me?

[Anonymous]

@Anonymous , I suggest you create a new Calculated Column of data tye Date. You will need to work out the customised logic to work out the correct Date value from the text values in your source data. I don't think there is a standard function to do this for you, given the examples you mention.

[Anonymous]

Thanks. But I have more than one date each (Sep-2022 3times , Oct-2022 10 times etc.)

[Anonymous · Accepted solution]

@Anonymous , you can create a New Column with this DAX expression:

Date = 
    SWITCH(TRUE()
        , LEFT('YourTable'[SourceDate], 3) = "Jan", DATE(RIGHT('YourTable'[SourceDate], 4), 1, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Feb", DATE(RIGHT('YourTable'[SourceDate], 4), 2, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Mar", DATE(RIGHT('YourTable'[SourceDate], 4), 3, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Apr", DATE(RIGHT('YourTable'[SourceDate], 4), 4, 1)
        , LEFT('YourTable'[SourceDate], 3) = "May", DATE(RIGHT('YourTable'[SourceDate], 4), 5, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Jun", DATE(RIGHT('YourTable'[SourceDate], 4), 6, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Jul", DATE(RIGHT('YourTable'[SourceDate], 4), 7, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Aug", DATE(RIGHT('YourTable'[SourceDate], 4), 8, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Sep", DATE(RIGHT('YourTable'[SourceDate], 4), 9, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Oct", DATE(RIGHT('YourTable'[SourceDate], 4), 10, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Nov", DATE(RIGHT('YourTable'[SourceDate], 4), 11, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Dec", DATE(RIGHT('YourTable'[SourceDate], 4), 12, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Q-1", DATE(RIGHT('YourTable'[SourceDate], 4), 1, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Q-2", DATE(RIGHT('YourTable'[SourceDate], 4), 4, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Q-3", DATE(RIGHT('YourTable'[SourceDate], 4), 7, 1)
        , LEFT('YourTable'[SourceDate], 3) = "Q-4", DATE(RIGHT('YourTable'[SourceDate], 4), 10, 1)
        , LEFT('YourTable'[SourceDate], 1) = "Y", DATE(RIGHT('YourTable'[SourceDate], 4), 1, 1)
    )

With the sample dates you listed, this gives these results:

I hope this helps.