Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
boyddt_mn
Helper III
Helper III

Sort Month Year Chronologically

hello and thank you in advance for any help offered.

I've searched through the forums and found a lot of solutions but none of them have worked. The ones I've tried are listed below

 

280635 

957680 

299044 

 

I am using DirectQuery data for my mode and I have created a DateKey table and created a relationship between that and my data table. The data is a view from SQL. In my DateKey table I have a column created as 
YnMnAsInteger = (DateKey[Year])&FORMAT(DateKey[Date],"MM")

The relationship is between DOI_Date and MnYn = FORMAT(DateKey[Date],"MM")&"/" &DateKey[Year]. 

When I choose Sort by Column | YnMnAsInteger nothing changes.

 

I'm not sure what I'm missing.

 

DateKey_Table.PNGViewOutput.PNGVisual.PNGRelationships.PNGSortByColumn.png

1 ACCEPTED SOLUTION

@boyddt_mn 

In your table, Month-year should have been sorted or MonthyearInt or Sort column.

In YYYYMM format.

Also, the month year which uses in the report/visual should come from the same table as a sort column.

View solution in original post

16 REPLIES 16
amitchandak
Super User
Super User

Create a month sort column and mark it as sort column

 

Sort_by_column.pngSortnewribbion.png

@amitchandak, I have a column setup already, MonthY. I added that to the Axis entry along with the DOI_Date from the data table and again nothing changed.

 

In the relationship screen there is a warning message, "This relationship has cardinality Many-Many. This should...." it won't let me change it. Could that be causing my issue?

 

David

 

MonthYear.PNG

Yes. Avoid many-to-many unless you really need it. Bridge Tables generally work better for most use cases, and avoid problems M2M can cause.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans & @amitchandak , Ok, so I updated the view in SQL and added the full date colum and created a new EHS_Date column in my DataKey table to match the formatting between the two tables. The relationship screen allowed me to create a many to one relationship between the tables. But this did not change the behaviour. I have to entries in the DataKey table that are technically the same but the data is derived differently. Sort is created with Sort = FORMAT([Date],"YYYYMM") and YnMnAsInteger is created with YnMnAsInteger = (DateKey[Year])&FORMAT(DateKey[Date],"MM"). They produce the same value but they act differently. If I use sort under Axis and Sort By Column it arranges the colums as 04/2018, 06/2018, 03/2018... If I use YnMnAsInteger it sorts the columns as 01/2018, 01/2019, 01/2020, 02/2018, 02/2019, 02/2020...

 

I can output the view to a csv file and include the pbix if you think that'll help.

 

Again, thank you for the help.

 

DateKey_TableWithEHS_Date.PNGViewOutputWithDate.PNGVisualization_Sort.PNGVisualization_YnMnAsInteger.PNG

Hi @boyddt_mn ,

 

We can create the sort column in fact table as following, then make [DOI_Date] column sort by it to meet your requirement:

 

Sort = YEAR([DOI])*100+MONTH([DOI])

 

16.jpg17.jpg

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@boyddt_mn 

In your table, Month-year should have been sorted or MonthyearInt or Sort column.

In YYYYMM format.

Also, the month year which uses in the report/visual should come from the same table as a sort column.

Hi @amitchandak

I have done the soring based on short month year but still not getting this sorted correctly in my visual report. 

mamunabdullah_0-1630679550478.png

 

mamunabdullah_1-1630679895587.png

 

TIA

 

 

You might want to start a new thread @mamunabdullah since this one was solved over a year ago, and provide some data, not just "it isn't working." For example, I cannot see what you are sorting by, or how you have the chart sorted. 

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@amitchandak & @edhans , I am going to mark this as the accepted solution. I rang a co-worker and did a screen share with him and he pointed out that I had to force the table to sort by the "Sort" field and @amitchandak stated this here. I just didn't realize what was being requested until I had my co-worker on the line. Thank you both for all of your help.

 

That is what I was trying to convey here
https://community.powerbi.com/t5/Desktop/Sort-Month-Year-Chronologically/m-p/987338/highlight/true#M...

Glad you got it fixed.


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@amitchandak, OK, so I created a sort column in the SQL view, refreshed the data and used DOI_Date and the new Sort column from the view and same thing. Switched to DOI and it appeared to work but since it was each day the graph wasn't clear attachment included. What is interesting is if Sort is first in the Axis selection and DOI is in 2nd place the graph works as expected. The labels are off since it is presenting a integer instead of a date but I have things moving in the right direction.

 

Sort_DOI.PNGDOI_by_Day.PNG

@amitchandak, per your suggestion I pulled both the Sort column (Sort = FORMAT([Date],"YYYYMM")) and the MonthY column (

MonthY = FORMAT(DateKey[Date],"MMM")&" " &DateKey[Year]) from the DateKey table.  And there isn't any change to the the sort order. I get 04/2018, 06/2018, 03/2018, 07/2019 ... in the graphic. 
 
Totally frustrating.

Make sure your visual is sorted by the date field you want. Often it is sorted by the data values. So sort by MM/YYYY field, have the date field sorted by your YYYYMM column, then ensure the visual is actually sorting on the MM/YYYY text field you have. Use the Visual Elipses menu to verify how it is sorted.

 

As a slight aside, if you can push most of this logic back into Power Query your model will perform better. Calculated Columns can increase the model size and slow it down compared to native data. I use the MM/YYYY sort technique but in Power Query, not with a FORMAT() function. See this article for more.

 

If you still cannot get it to sort right, can you share the PBIX via OneDrive?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans , I won't be able to share it until this evening. My company lock down access to cloud storage so I'll have to email the file to myself and share it from my home computer. Should I out put the results of the view and include it as a CSV file? As per my last post I'm getting close but missing some pieces. 

That should be ok, but please do NOT share confidential data your employer doesn't want you to see. If you are nervous about it, you can PM me the link and only I will see it. I will not be able to tinker with it until this evening (about 7 hrs away for me) though.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans, I got pulled away from this project and am just now getting back to it. I'm going to play with it some more and if I cannot figure it out I'll get you the files this evening.

 

David

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.