Forum Discussion
Sort Month Year String Chronologically
Hi,
I am using Power Pivot and I am trying to sort the column DateFirstPurchase by Month and Year String chronologically, in an independent column. Currently, I am able to do it in my pivot table, but it requires the Month Year Order column to be added as row. Can you help create a column that sorts the Month Year String column chronologically and independend from another column.
FYI.. this is a snapshot of the table entitled Customers it goes across 37 months.
| Month Year Order | Month Year String | Year | Month | DateFirstPurchase |
| 200304 | 3-Apr | 3 | Apr | 4/1/2003 0:00 |
| 200304 | 3-Apr | 3 | Apr | 4/4/2003 0:00 |
| 200304 | 3-Apr | 3 | Apr | 4/4/2003 0:00 |
| 200304 | 3-Apr | 3 | Apr | 4/8/2003 0:00 |
| 200304 | 3-Apr | 3 | Apr | 4/9/2003 0:00 |
| 200304 | 3-Apr | 3 | Apr | 4/10/2003 0:00 |
| 200304 | 3-Apr | 3 | Apr | 4/13/2003 0:00 |
| 200304 | 3-Apr | 3 | Apr | 4/13/2003 0:00 |
| 200304 | 3-Apr | 3 | Apr | 4/22/2003 0:00 |
| 200304 | 3-Apr | 3 | Apr | 4/25/2003 0:00 |
| 200304 | 3-Apr | 3 | Apr | 4/30/2003 0:00 |
| 200304 | 3-Apr | 3 | Apr | 4/30/2003 0:00 |
Hi gbrenner35 ,
If you use format conversion directly, the field type of the created column is Text, and you need to use the value function to convert it to a numeric type.
FORMAT('Table'[Month Year String],"YYYYMM")Col_Format = VALUE(FORMAT('Table'[Month Year String],"YYYYMM"))
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
Hi gbrenner35 Greg
It should be as simple as sorting. If you haven't aready, can you convert the Month Year to a Whole Number, then try to sort?
In Power BI Desktop, you'd use this to convert:
Thanks heaps,
Theogbrenner35 just took a look at your working file. It looks as though your Month Year is in text format rather than numeric which is stopping you from being able to sort by Month Year. Please convert it to Whole Number and then you will be able to sort by Month Year 🙂
Hi Theo,
The Month and year is a string, when I tried to convert it to a whole number, I recieved an error message.
Thank you,
Greg
Hi Greg gbrenner35
Can you create a Calculated Column as follows:
New Column = VALUE ( Table[Month Year] )
This converts it to a value and you can then use it to sort 🙂
Hi Theo,
I turned month year into a string when I did folloiwng steps, but now it will not let me turn my text into a whole number. Should I have generated month year a different way?
=Format(Customers[DateFirstPurchase], "MMM YY")Attached is what I see in Power Pivot.
Thank you,
Greg
Hi gbrenner35
Can you modify that to:
= VALUE( FORMAT ( Customers[DateFirstPurchase] , "MMM YY") ) )
Hi Theo,
I used this equation, but it turned my text string into a number, I want the text string to be visible in my pivot table, but in chronologically. I have seen people do this in a calculated column using the earlier function, but I do not have much experience with the earlier function.
= VALUE( FORMAT ( Customers[DateFirstPurchase] , "MMM YY") ) )
Cheers,Greg
Here is another link I found on this topic, but I am having an issue applying this code to my data set.
https://community.powerbi.com/t5/Desktop/sorting-a-date-table-chronologically/m-p/281960#M125537
Hi gbrenner35 ,
If you use format conversion directly, the field type of the created column is Text, and you need to use the value function to convert it to a numeric type.
FORMAT('Table'[Month Year String],"YYYYMM")Col_Format = VALUE(FORMAT('Table'[Month Year String],"YYYYMM"))
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
