Order date in a table

1
votes

Hi I'm trying to order a table in BI by monthNumber, but now I have same months in differents year, and PowerBI doesn't care about that. I have:

  1. Jan-20
  2. Feb-19
  3. Feb-20
  4. Mar-19
  5. Mar-20
  6. Apr-19
  7. May-19
  8. Jun-19
  9. Jul-19
  10. Aug-19
  11. Sep-19
  12. Oct-19
  13. Nov-19
  14. Dec-19

And I want:

  1. Feb-19
  2. Mar-19
  3. Apr-19
  4. May-19
  5. Jun-19
  6. Jul-19
  7. Aug-19
  8. Sep-19
  9. Oct-19
  10. Nov-19
  11. Dec-19
  12. Jan-20
  13. Feb-20
  14. Mar-20

NOTE: Each month I will add a new month (The actual current month e.g. Now Aug-20) and this should still work properly

1
powerbi

1 Answers

2
votes

Let your table name is "order_by_month" and the column name is "month_year". Now just follow this following steps to order data as per your requirement.

Step-1: In Power Query, create a custom column as below image-

enter image description here

We are generating this column with the First date of each month so that we can order our original data later based on the Date value. The generated value in the new column will look like below-

enter image description here

Step-2: Change the above red marked column's data type as Date.

Step-3: Get back to report by clicking the "Close & Apply" Button.

Step-4: In the report, go to Table view and select your original column "month_year" and order this column by newly created column "date_formatted_value" as shown in the beloe image.

enter image description here

Now the final output will be sorted as below as you wants-

enter image description here