Forum Discussion
Sort an array by year and month
Good everyone.
I have a table, which has no date, but a month column and a year column, plus other columns of calculation of values:
To which I made an "unpivot" to display the data so that the column names remain as rows for what I need (the capture does not show all the total records, but the idea is understood):
All this to be able to show the following form in a matrix:
So far so good, but my problem comes at the time of ordering, since I need to show the columns by descending year and descending month:
2023 - 2022
1 - 12 - 11 - 10 - 09 - 08 - 07 - 06.... etc
Try creating a separate table with the months in number and the name of the month, but it does not work and it does not help me to create a calendatio table and establish an order there, because as you can see, it only has fields month and year.
I hope you can guide me.
5 Replies
You can do that using separate columns that you sort the other column by.
Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi,
Write this calculated columnn formula to create a Date column
Date = 1*("1/"&Data[Mes]&"/"&Data[Anio])
Write this calculated column formula to create a Calendar Table
Calendar = calendar(min(Data[Date]),max(Data[Date]))
In the Calendar Table, create calculated column formulas for Year, Month name and Month number. Sort the Month name by the Month number. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table. To your visual, drag Year and Month name from the Calendar Table.
Hope this helps.
Here I attach the excel with which I load the data and the pbix with the steps to load the information, so you have more clarity.
https://www.mediafire.com/file/fqp60bi8sh79wt6/Pronostico.xlsx/file
https://www.mediafire.com/file/asfduno187dumg0/Prueba+Pronostico.pbix/file
Here is a sample implementation
Table = DATATABLE("Mes",INTEGER,"Mesno",INTEGER,{{1,12},{2,11},{3,10},{4,9},{5,8},{6,7},{7,6},{8,5},{9,4},{10,3},{11,2},{12,1}})See attached.
Thank you very much for the help!
This solution worked perfectly 👌
