Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I try to sort year & month. I want to make the latest year & month show in the front. For example, Y20M06 then Y20M05 then Y20M04. But year & month datatype is in text, so i cannot sort using simple sorting method
So i make a table for index like below:
The year & month in this table, i link together with the year & month in another table that i desire. But the result still the same. i cant sort the year & period. Really appreciate if anyone can help me with this problem. Thanks in advance!
Solved! Go to Solution.
Hi @New_be ,
If you want the latest date to be at the top, you should descend first, then add an index.
You can refer the following steps.
1. We need to create a unique Year&Month table. And sort the column.
2. Then create an index column.
3. Create an one-to-many relationship with Actual table.
4. At last, in the matrix table visual, put the Table[Year&Month] to Column and sort it by Index.
If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data and describe the relations between tables?
It will be helpful if you can show us the exact expected result based on the tables.
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @New_be ,
If you want the latest date to be at the top, you should descend first, then add an index.
You can refer the following steps.
1. We need to create a unique Year&Month table. And sort the column.
2. Then create an index column.
3. Create an one-to-many relationship with Actual table.
4. At last, in the matrix table visual, put the Table[Year&Month] to Column and sort it by Index.
If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data and describe the relations between tables?
It will be helpful if you can show us the exact expected result based on the tables.
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Great explaination! Thank you so much!
@New_be - Are you using Sort By of your Index column for your Year & Month? You may need to reverse your ordering in your Index so that you go from 21 - 1 instead of 1 - 21
@New_be , if you want that on row or axis. You should be able to do it with three dots. with sort descending.
In case you need for matrix column.
Create a desc rank on you year month
Create new columns
Month Year Rank = RANKX(ALL('Date'),'Date'[Month Year],,DESC,Dense)
Month Year 1 = [Month Year]
Sort this new Month Year 1 on Month Year Rank and use that. As Power bi might not allow you to sort Month Year on Month Year Rank .
Great answer! thanks for your time. Really appreciate that!
Hi,
Why do you have a Many to Many relationship between the Calendar and Actual tables?
Great answer! thanks for your time. Really appreciate that
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |