Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.