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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Employee
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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.