I can't sort columns with date headers from newest to oldest.

[Anonymous]

Hi.  I'm building a standard financial report - a P&L Statement.  I need to show 3 columns representing, separately, the transaction amounts in 3 consecutive months.  I've tried every way I can imagine or Google, but I cannot get the columns to appear in order from the most recent month selected to the oldest month selected, left to right.

I'm using a date table, but I've also tried using the date directly on the financial transaction records.  In the date table I have a YYYY-MM column, which sorting acending or descending doesn't make a difference.  I added an index column after sorting the date table by date descending - that doesn't work... not sorting the table by that column, not sorting the table by date descending, not sorting the YYYY-MM column by the index or the date ... I have a MonthInYear column - sorting that column or sorting any other column by that column doesn't work.  I've used the date field directly - with and without a hierarchy - doesn't work.  Nothing I do will make this show Sep-Aug-Jul instead of Jul-Aug-Sep.  Why?  Thanks.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Hi lbendlin.  Thank you for looking at my inquiry.  I can't provide the data I'm using because it's real financial data from my company.  But it's not complex:

I have a transaction table - General Ledger Entries - and in it, each record has a date, an account number, and an amount (those are the relevant fields/columns).

I have a date table, created with a function that lists as a record, every date from 1/1/2018 - 12/31/2030.  For each date record there are columns with different "formats" for each date value, with the YYYY-MM format being relevant.

The date table is joined to the transaction table on the Date column.

I'm creating a matrix that uses Transactions.AccountNumber as the row values (it's more than that, but for simplicity's sake), Transactions.Amount as the values, and Dates.YYYY-MM as the columns.

The colunms show up in ascending order from left to right, e.g. 2022-01, 2022-02, and 2022-03.

I simply want them to show up in the opposite order 2022-03, 2022-02, and 2022-01.

The YYYY-MM column in the date table is formatted as Text.

If I sort Dates.YYYY-MM in descending order, it does nothing to the order in which the columns are displayed in the matrix.  If I sort I sort Dates.Date in descending order, it does nothing.

I'm trying to figure out now how to create a column and assign a unique, sequential numerical value to each distinct YYYY-MM value - which seems ridiculous since YYYY-MM is exactly that, only not numerical ... surely Power BI can sort text.

Any help is appreciated.  Thanks.

[Anonymous]

So, in my date table I created a YearMonthIndex column that sequentially numbers each distinct YYYY-MM value with a whole number.  Then I created an InverseYearMonthIndex column with the negative value of YearMonthIndex, and I sorted YYYY-MM by that, which produced the desired result.

But that's a lot of work to sort these columns - and this is a VERY COMMON view in financial reporting, both the YYYY-MM column and having them sorted newest to oldest, left to right.  Sorting the YYYY-MM column should do it directly... or at least sorting the underlying Date column descending should accomplish it.  But it doesn't.  I'd love to understand why.

[lbendlin]

Any sortable column in a Power BI visual can be sorted either ascending or descending.  In addition you can enforce sorting by using the "sort one column by another column"  feature.

Cannot help further without sample data.