Forum Discussion
Sorting Column with different date formats
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 , 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.
Thanks. But I have more than one date each (Sep-2022 3times , Oct-2022 10 times etc.)
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.