cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gbrenner35
Frequent Visitor

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 OrderMonth Year StringYearMonthDateFirstPurchase
2003043-Apr3Apr4/1/2003 0:00
2003043-Apr3Apr4/4/2003 0:00
2003043-Apr3Apr4/4/2003 0:00
2003043-Apr3Apr4/8/2003 0:00
2003043-Apr3Apr4/9/2003 0:00
2003043-Apr3Apr4/10/2003 0:00
2003043-Apr3Apr4/13/2003 0:00
2003043-Apr3Apr4/13/2003 0:00
2003043-Apr3Apr4/22/2003 0:00
2003043-Apr3Apr4/25/2003 0:00
2003043-Apr3Apr4/30/2003 0:00
2003043-Apr3Apr4/30/2003 0:00
 
Thank you,
Greg
1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

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")

vhenrykmstf_0-1636528704545.png

 

Col_Format = VALUE(FORMAT('Table'[Month Year String],"YYYYMM"))

vhenrykmstf_1-1636528729608.png


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.

View solution in original post

10 REPLIES 10
v-henryk-mstf
Community Support
Community Support

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")

vhenrykmstf_0-1636528704545.png

 

Col_Format = VALUE(FORMAT('Table'[Month Year String],"YYYYMM"))

vhenrykmstf_1-1636528729608.png


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.

gbrenner35
Frequent Visitor

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 

gbrenner35
Frequent Visitor

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") ) )

image (1).png 


Cheers,

Greg

 

 

 

gbrenner35
Frequent Visitor

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,

GregCapture.PNG

Hi @gbrenner35 

 

Can you modify that to:

 

= VALUE( FORMAT ( Customers[DateFirstPurchase] , "MMM YY") ) )

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

gbrenner35
Frequent Visitor

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] )

 

TheoC_0-1635821265129.png

 

This converts it to a value and you can then use it to sort 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

gbrenner35
Frequent Visitor
TheoC
Super User
Super User

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:

TheoC_0-1635820962220.png

 

Thanks heaps,
Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@gbrenner35 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 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors