Sort Aging Accounts Receivable

[MojoGene]

With a lot of help and inspiration from the posts here, I have put together a very useful aging AR report, but getting the right sort order for the overdue time groups ("0-30 Days", "31-60 Days", "61-90 Days", etc.) has been, ah, challenging.

The data table, "Bills", is straightforward and has all the usual fields for [Customer], [Bill Amount], [DateBilled], [Days Overdue], etc. [DateBilled] is linked to my DimDate table.

I created the following field in the table to group the bills by [Days Overdue]:

Aging = IF(TODAY()-Bills[DateBilled]<=30,"0-30",IF(TODAY()-Bills[DateBilled]<=60,"31-60",IF(TODAY()-Bills[DateBilled]<=90,"61-90",IF(TODAY()-Bills[DateBilled]<=180,"91-180",IF(TODAY()-Bills[DateBilled]<=365,"181-365",IF(TODAY()-Bills[DateBilled]<=730,"366-730","Over 730"))))))

This field [Aging] correctly sorts the overdue bills into the proper groups.

I have a separate table "AR Sort Order" that looks like this:

SortCode     AgingGroup	            Min	   Max	     Order
1	                0-30	      0	   30	          1
2	               31-60	     31	   60	          2
3	               61-90	     61	   90	          3
4	               91-180	     91	   180	          4
5	              181-365	    181	   365	          5
6	              366-730	    366	   730	          6
7	              Over 730	    731	   3650	          7

At this point, I am lost trying to get the overdue groups to sort in the above order. Instead, they are sorting into the number of overdue bills in each group.

Any help appreciated. Thanks.

[MattAllington]

In the data view in the table, there is a "sort by column" button. Select the text column, click sort by column and then select the sort order column. 

[MojoGene]

Matt:

Thanks for jumping in here. I should have explained my dilemma a little better.

I follow what you are saying about sorting the text column by the sort order column, but my problem goes back to a prior step, i.e., getting the Bills table to "look to" the AR Sort Order table.

I was thinking that I would need to create a colum in the Bills table that incorporates the sort Order field from the AR Sort Order table, the logic of this being: IF(Bills[Aging]='AR Sort Order'[AgingGroup] THEN 'AR Sort Order'[Order]. I have tried to define such a column in the Bills table using the RELATED function, but I am hitting a brick wall.

This may or may not be related. The AR Sort Order table in my data model, which comes from an Excel spreadsheet, shows no rows. Here is how it looks in the Query Editor and then in the table view:

I have tried refreshing the table, but still no rows come into the t