0

I'm attempting to create an aging buckets analysis through the use of Excel Power Query.

Thus far I've created a column calculating the age of each record. The step I'm currently on is to categorize each record into age buckets such "31-60 days overdue" etc. This is being done though the use of a custom column with the following formula:

=if [Payment Due Date] >180 then "over 180 days due" else if [Payment Due Date] <= 180 and [Payment Due Date] > 150 then "151-180 days overdue" else if [Payment Due Date] <= 150 and [Payment Due Date] > 120 then "121-150 days overdue" else if [Payment Due Date] <= 120 and [Payment Due Date] > 90 then "91-120 days overdue" else if [Payment Due Date] <= 90 and [Payment Due Date] > 60 then "61-90 days overdue" else if [Payment Due Date] <= 60 and [Payment Due Date] > 30 then "31-60 days overdue" else if [Payment Due Date] <= 30 and [Payment Due Date] > 0 then "1-30 days overdue" else if [Payment Due Date]<= 0 and [Payment Due Date] > -30 then "1-30 days overdue" else if [Payment Due Date] <= -30 and[Payment Due Date] and [Payment Due Date] > -60 then "31-60 days overdue" else if [Payment Due Date] <= -60 and [Payment Due Date] > -90 then "61-90 days overdue" else if [Payment Due Date] <= -90 and [Payment Due Date] > -120 then "91-120 days overdue" else if [Payment Due Date] <= -120 and [Payment Due Date] > -150 then "121-150 days overdue" else if [Payment Due Date] <= -150 and [Payment Due Date] > -180 then "151-180 days overdue" else if [Payment Due Date] <= -180 then "over 180 days overdue" else 0

But I get the following error: Expression.Error: *We cannot apply operator < to types Number and Date. Details: Operator=< Left=180 Right=5/3/2021* here are some pictures for you to assess Error Message Custom Column Formula View

Lwazi Mace
  • 15
  • 4
  • Add a custom column that subtracts the due date from today's date, then use that instead of the due date in your calculation. – Rory Jun 22 '21 at 09:58
  • Rory... You just made me realize that I used the wrong column/field. Dude! Thank you. Thats such a dumb mistake glaring at me – Lwazi Mace Jun 22 '21 at 11:08

1 Answers1

0

=if [Days Past Due Date] >180 then "over 180 days overdue" else if [Days Past Due Date] <= 180 and [Days Past Due Date] > 150 then "151-180 days overdue"
else if [Days Past Due Date] <= 150 and [DaysPast Due Date] > 120 then "121-150 days overdue"
else if [Days Past Due Date] <= 120 and [Days Past Due Date] > 90 then "91-120 days overdue"
else if [Days Past Due Date] <= 90 and [Days Past Due Date] 60 then "61-90 days overdue"
else if [Days Past Due Date] <= 60 and [Days Past Due Date] > 30 then "31-60 days overdue"
else if [Days Past Due Date] <= 30 and [Days Past Due Date] > 0 then "1- 30 days overdue"
else if [Days Past Due Date] <= 0 and [Days Past Due Date] > -30 then "1-30 days due"
else if [Days Past Due Date] <= -30 and [Days Past Due Date] > -60 then "31-60 days due"
else if [Days Past Due Date] <= -60 and [Days Past Due Date] > -90 then "61-90 days due" else if [Days Past Due Date] <= -90 and [Days Past Due Date] > -120 then "91-120 days due"
else if [Days Past Due Date] <= -120 and [Days> Past Due Date] > -150 then "121-150 days due"
else if [Days Past Due > Date] <= -150 and [Days Past Due Date] > -180 then "151-180 days due" else if [Days Past Due Date] <= -180 then "over 180 days due"
else 0

Lwazi Mace
  • 15
  • 4
  • I think the problem was that I was tired during my first attempt – Lwazi Mace Jun 22 '21 at 13:25
  • You can greatly simplify your existing formula. `... else if [Days Past Due Date] <= 180 and [Days Past Due Date] > 150 then ... => else if [Days Past Due Date] > 150 then ...` And there may be an even simpler algorithm. – Ron Rosenfeld Jun 22 '21 at 20:31