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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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