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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
tlwest86
Helper I
Helper I

Sort By Column Not Working / Help with Dates Table

I created a Matrix Visualization to display progress over my Agency's fiscal year, which starts in October (seen in the first screenshot).  I used a date table (seen in the 2nd screenshot) which I have used several times in the past, but it is no loner working as expected.  I previously achieved this by creating a column called 'Fiscal Year' that assigned October as 1, November as 2, December as 3, and so forth.  I then clicked the 'Sort By Column' button and selected the 'Fiscal Year' column (also 2nd screenshot).  I want the columns to be arranged by the 'Fiscal Year' column, which means the columns should be Oct, Nov, Dec, then Jan, but they are sorted alphbetically. 

 

I know my quarter column is not coded right so I could use some help there as well.  I put the code of the Dates table at the bottom.  Also, is there a way to add weeks to the my dates table?

tlwest86_0-1641593898892.png

My dates table looks like this:

tlwest86_1-1641593953875.png

Dates =
VAR BaseCalendar =
CALENDARAUTO(9)
RETURN
GENERATE (
BaseCalendar,
VAR BaseDate = [DATE]
VAR YearDate = YEAR (BASEDATE)
VAR MonthNumber = MONTH (BASEDATE)
VAR Quarter = QUARTER(BASEDATE)
RETURN ROW (
"Day", BaseDate,
"Year", YearDate,
"Month Number", MonthNumber,
"Quarter", BaseDate,
"Month", FORMAT (BaseDate, "mmmm"),
"Year Month", FORMAT (BaseDate, "mmm yy")
 
))
1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.pngUntitled1.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

12 REPLIES 12
mahoneypat
Microsoft Employee
Microsoft Employee

It looks like your Date, Day, and Quarter columns are DateTime instead of Date. I would change those data types.  Also, please see these two articles, that include M and/or DAX code to create Date tables that do not require Sort By Columns and a way to make a fiscal calendar that includes weeks.

No Sort Date Tables! – Hoosier BI

445 Calendar with 53-Week Years – Hoosier BI

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thank you so much for the resources!

Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.pngUntitled1.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you so much for your help.  What did you do to get this result?

You are welcome.  If my previous reply helped, please mark that as Answer.  I clicked on any one cell in the Month column of the Dates table and then clicked on Fiscal Year (refer first screenshot). 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Wow!  I was clicking on the column header and not on an actual cell.  Thanks again!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
tlwest86
Helper I
Helper I

No matter which column I sort by, the visulization does not change.  Since this has worked in the past, I recreated those reports with the same queries and still cannot get the months to display other than alphabetically. 

tlwest86
Helper I
Helper I

That did not work.

amitchandak
Super User
Super User

@tlwest86 , click on month and mark this column as sort column , You are marking it for itself

 

Example . selected column is month

 

Sort Month.png

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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