Calculated Column help - Nested If - says type mismatch Ugh!

[wethernt]

Can't figure out what I am doing wrong for the life of me... Yes, it long... trying to bucket dates into 8 different buckets as follows: 2013, 2014, >120days&YR15, 91-120, 61-90, 31-60, 1-30, Current Thanks for any help.


=IF([Invoice Due Date]<=DATEVALUE(12/31/2013),"2013",
IF([Invoice Due Date]<=DATEVALUE(12/31/2014),"2014",
IF(AND([Invoice Due Date]<=DATEVALUE(12/31/2015), [Invoice Due Date]<(TODAY()-120)),"120+YR15",
IF(AND([Invoice Due Date]>=(TODAY()-119), [Invoice Due Date]<(TODAY()-90)),"91-120",
IF(AND([Invoice Due Date]>=(TODAY()-90), [Invoice Due Date]<(TODAY()-60)),"61-90",
IF(AND([Invoice Due Date]>=(TODAY()-60), [Invoice Due Date]<(TODAY()-30)),"31-60",
IF(AND([Invoice Due Date]>=(TODAY()-30), [Invoice Due Date]<(TODAY()-1)),"1-30",
"Current")))))))

 

[Matt Allington]

Are you loading this to Power Pivot? There is a much easier way to do it there. Aged Debtors–Dynamic Banding in DAX - PowerPivotPro or DAX Patterns: Banding, New vs Old, Many-to-many - SQLBI

From what I can see, you are missing a single closing bracket at the end.

 

[wethernt]

Are you loading this to Power Pivot? There is a much easier way to do it there. Aged Debtors–Dynamic Banding in DAX - PowerPivotPro or DAX Patterns: Banding, New vs Old, Many-to-many - SQLBI

From what I can see, you are missing a single closing bracket at the end.

Thanks for the link. Actually, I had used the article you reference extensively prior to abandoning it because it was a bit advanced for me. I will re - review the article and see if I can implement. I have since found this forum and registered so perhaps any obstacles can me be mitigated with help here. Thanks again.

 

[wethernt]

Thanks for the link. Actually, I had used the article you reference extensively prior to abandoning it because it was a bit advanced for me. I will re - review the article and see if I can implement. I have since found this forum and registered so perhaps any obstacles can me be mitigated with help here. Thanks again.

I realize that some of my Invoice Dates are blanks. Is this why my nested if is not working?

 

[Matt Allington]

I think I worked out the problem. An if statement must return the same data type for every answer. You have test answers for everything except the one that returns a measure. First try changing the measure to a random text word. If that fixes it, try converting the measure to a text equivalent with a suitable formula. Not sure what is available but probably format() or something similar

 

[wethernt]

I think I worked out the problem. An if statement must return the same data type for every answer. You have test answers for everything except the one that returns a measure. First try changing the measure to a random text word. If that fixes it, try converting the measure to a text equivalent with a suitable formula. Not sure what is available but probably format() or something similar

Thanks - sounds reasonable and I do hope it works. However, I am unsure which of my logical return items is text. Can you clairify? Thanks again very much.

 

[PentaGalCXO]

The nested IF()s should work, but for clarity I suggest switch(true()), eloquently explained here and here.

For your example it would be something like:

=switch(true(),
[Invoice Due Date]<=DATEVALUE(12/31/2013),"2013",
[Invoice Due Date]<=DATEVALUE(12/31/2014),"2014",
AND([Invoice Due Date]<=DATEVALUE(12/31/2015), [Invoice Due Date]<(TODAY()-120)),"120+YR15",
AND([Invoice Due Date]>=(TODAY()-119), [Invoice Due Date]<(TODAY()-90)),"91-120",
AND([Invoice Due Date]>=(TODAY()-90), [Invoice Due Date]<(TODAY()-60)),"61-90",
AND([Invoice Due Date]>=(TODAY()-60), [Invoice Due Date]<(TODAY()-30)),"31-60",
AND([Invoice Due Date]>=(TODAY()-30), [Invoice Due Date]<(TODAY()-1)),"1-30",
"Current")

 

[wethernt]

The nested IF()s should work, but for clarity I suggest switch(true()), eloquently explained here and here.

For your example it would be something like:

=switch(true(),
[Invoice Due Date]<=DATEVALUE(12/31/2013),"2013",
[Invoice Due Date]<=DATEVALUE(12/31/2014),"2014",
AND([Invoice Due Date]<=DATEVALUE(12/31/2015), [Invoice Due Date]<(TODAY()-120)),"120+YR15",
AND([Invoice Due Date]>=(TODAY()-119), [Invoice Due Date]<(TODAY()-90)),"91-120",
AND([Invoice Due Date]>=(TODAY()-90), [Invoice Due Date]<(TODAY()-60)),"61-90",
AND([Invoice Due Date]>=(TODAY()-60), [Invoice Due Date]<(TODAY()-30)),"31-60",
AND([Invoice Due Date]>=(TODAY()-30), [Invoice Due Date]<(TODAY()-1)),"1-30",
"Current")

Thanks for your help. I was interested in having previously read about "switch". I used the statement you placed here and got the same message "type mismatch". Just so all know and I am being clear, I am using this as a calculated column in PowerPivot. The result is #ERROR and it says Type mismatch. I am simply perplexed! Thanks for sticking with me through this.

 

[wethernt]

Are you loading this to Power Pivot? There is a much easier way to do it there. Aged Debtors–Dynamic Banding in DAX - PowerPivotPro or DAX Patterns: Banding, New vs Old, Many-to-many - SQLBI

From what I can see, you are missing a single closing bracket at the end.

Here is my attempt to create this solution using the Dynamic Banding. Several screens below that show ultimately, my results in my pivot are being duplicated across the bands. Probably an easy fix for any more advanced than myself. Not sure. Thanks for looking.

Image included with this post.....

 

[KHorseman]

I think the type mismatch may be because the first two values you're returning look like whole numbers and others look like mixed text. Try nesting the "2013" and "2014" in a function that explicitly returns a text value. Like CONCATENATE("2013","") maybe.