CODEWITHSUNDEEP
excelpowerbipowerquerycustomcolumn
Lwazi Mace
Lwazi Mace

Reputation: 15

Please help me resolve my Excel Power Query / Custom Column Error

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

Upvotes: 0

Views: 367

Answers (1)

Lwazi Mace
Lwazi Mace

Reputation: 15

=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

Upvotes: 0

Related Questions