Forum Discussion
sort data on date column
I want the data in a visualization sorted by date field. How can I achieve this?
X-axis will show Item Name, Y-axis will show Total Parts. But the items should be sorted by "Date Added" column.
Table structure:
Item Name, Total Parts, Date Added
Any help? Thanks.
3 Replies
This took me a second to figure out what you're asking for - the only option for this currently is to go into the "Data" section in Power BI and use the "Sort by Column" feature - this will sort one column by the results in another column.
Here is my data:
Item Name Pass Dies Total Dies Date Added Item A 50 70 28-Jan-16 Item A 70 90 12-Feb-16 Item A 115 145 15-Feb-16 Item A 230 300 27-Feb-16 Item A 18 50 10-Mar-16 Item A 67 100 17-Mar-16 Item B 68 150 12-Feb-16 Item B 119 200 27-Feb-16 Item B 268 400 17-Mar-16 Item B 742 1000 28-Mar-16 Item C 54 80 28-Jan-16 Item C 75 100 12-Feb-16 Item C 128 150 28-Mar-16 Item C 34 70 29-Mar-16 Item C 79 100 8-Apr-16 There are multiple dates for each item. So when I select "Item Name" column, click "Sort by Column" button and select "Date Added", it gives me following error:
"We cannot sort the 'Item Name' column by 'Date Added'. You can't have more than one value in 'Date Added' for the same value in 'Item Name'. Choose a different column for sorting or update the data in 'Date Added'.
So how can I possibly sort the items by 'Date Added', Any idea??
Is it possible that a new column be added in the existing table that contains max date for each respective item and use this new column as the 'Sort By Column'?
BTW how did you add Calendar table in your example?
Alright yes I see the issue. Your comment about the Calendar table is very relevant. You're data needs to be more "normalized"
Email me and I'll send you a worked solution in Excel ([email protected]). You need to break out the Items into a separate table and the dates into a separate table.
