Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Arrange date in correct order

Hi everyone,

 

I plotted a graph but the date structure is not arranged graphically. I got this column of dates from my excel file and It is in format Jan-16 which means January, 2016. I want to display the months in correct order and year but It is puzzled and I am not able to see the correct variation.

So this is the representation in front of me. How Can I sort it.

 

Thank you

 

Regards,

Diksha

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    Please create a custom column in power query:

     

    = Table.AddColumn(#"Changed Type", "Datefororder", each Text.Insert([No. of Working Days],4,"20"))

     

    Then convert it to date in data view:

     

    Then you can use sort by columns:

     

    Please refer to the pbix file.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

     

12 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for helping but It is not working. 

       

      Best Regards,

      Diksha Gupta

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, Anonymous 

    I suggest having one more column in your dim-date table for the purpose of sorting.

    For instance, you can create endofmonth column by using the date column and the EOMONTH function.

    Then you can sort the month-year column by the newly created column. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for replying Jihwan.

      I tried it but what to do in ENDOFMONTH Function?

      I'm not getting option to write the column name in it?

       

      Could you explain in detail. Header name for date column in format Jan-16 is No. of working days.

       

      Thank you

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

        Hi, Anonymous 

        Thank you for your feedback. 

        If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more relevant solution.

        Thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous a quick check , have you already tried the sort option on the visual ?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi,

      Yes, I already tried it but It is not working.

       

      Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        So, in the table of PBI, the "Date" column (you got from excel) has been marked as Data Type "Date"?

        If so, you may have to create another table for sort... as other replies introduced.

  • jaideepnema's avatar
    jaideepnema
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi Anonymous ,

    Can you please confirm if the data type of your date column is date or something else ?

     

    Regards,

    Jaideep Nema

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Please create a custom column in power query:

     

    = Table.AddColumn(#"Changed Type", "Datefororder", each Text.Insert([No. of Working Days],4,"20"))

     

    Then convert it to date in data view:

     

    Then you can use sort by columns:

     

    Please refer to the pbix file.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai