I am looking up a date value on a larger table so sometimes there will be a blank value and sometimes a date. I assume this is the reason I get the error about variant date types. Couple of questions:
1. I'm copying the formula from PowerPivot 2010 to PP 2016. Is this an "enhancement" in 2016 that didn't used to error in older versions? PP 2010 finds the date with no issue
2. I have been able to work round this by using an IF statement that checks if the lookup produces a blank, if so blank , otherwise the value. This works , but has made the calculation enormous as below. Is there a neater way to achieve the same thing as the original lookup as quite long? I tried IFERROR, but that doesn't work
Thanks Mike
1. I'm copying the formula from PowerPivot 2010 to PP 2016. Is this an "enhancement" in 2016 that didn't used to error in older versions? PP 2010 finds the date with no issue
2. I have been able to work round this by using an IF statement that checks if the lookup produces a blank, if so blank , otherwise the value. This works , but has made the calculation enormous as below. Is there a neater way to achieve the same thing as the original lookup as quite long? I tried IFERROR, but that doesn't work
Code:
=IF( CALCULATE(
VALUES('Posted Document Header'[Posting Date]),
FILTER('Posted Document Header',
'Posted Document Header'[VRM]=MOTLeads[VRM] &&
'Posted Document Header'[Posting Date]>[MOT Due Date]-35 &&
'Posted Document Header'[Posting Date]<[MOT Due Date]+60 &&
'Posted Document Header'[Inc MOT]>0 &&
'Posted Document Header'[Rank Vehicle Visit]=CALCULATE(
MIN('Posted Document Header'[Rank Vehicle Visit]),
FILTER('Posted Document Header',
'Posted Document Header'[VRM]=MOTLeads[VRM] &&
'Posted Document Header'[Posting Date]>[MOT Due Date]-35 &&
'Posted Document Header'[Posting Date]<[MOT Due Date]+60 &&
'Posted Document Header'[Inc MOT]>0
)
) )
)=BLANK(),
BLANK(),
CALCULATE(
VALUES('Posted Document Header'[Posting Date]),
FILTER('Posted Document Header',
'Posted Document Header'[VRM]=MOTLeads[VRM] &&
'Posted Document Header'[Posting Date]>[MOT Due Date]-35 &&
'Posted Document Header'[Posting Date]<[MOT Due Date]+60 &&
'Posted Document Header'[Inc MOT]>0 &&
'Posted Document Header'[Rank Vehicle Visit]=CALCULATE(
MIN('Posted Document Header'[Rank Vehicle Visit]),
FILTER('Posted Document Header',
'Posted Document Header'[VRM]=MOTLeads[VRM] &&
'Posted Document Header'[Posting Date]>[MOT Due Date]-35 &&
'Posted Document Header'[Posting Date]<[MOT Due Date]+60 &&
'Posted Document Header'[Inc MOT]>0
)
) )
)
)
Thanks Mike