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.
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.
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.
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
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |