I have run into this situation several times now: I use a Lambda defined/managed with AFE, and after the Lambda is edited, the pre-edited version of the lambda is used when it is referenced by another Lambda. It is as if the called Lambda has been "cached" inside the calling Lambda.
The only way I have found to "fix" the issue is to rename the "cached" lambda and change its usage throughout AFE and the workbook. This both very time consuming, as you spend hours tracking down what appears to be a bug only to find out it is actually just a wrong result being produced by Excel/AFE - and it is also very dangerous, as the tested Lambda is not guaranteed to be what is actually being run.
Here is an example from a complex workbook:
- There is a Lambda called Later.
- I call Later from my sheet and the result is 0, which is correct.
- I call Later from another Lambda called LaterTest and the result is -12836.69, which is wrong, and was the value that the Later lambda returned a few weeks ago before it was subsequently edited. LaterTest is a Lambda I *just made* so the "caching" is somewhere behind that.
- I duplicate the Later lambda word-for-word into a new lambda called Later2.
- I call Later2 from my sheet and the result is 0, which is correct.
- I call Later2 from another Lambda called Later2Test and the result is 0, as it should be.
To state the root issue very clearly: two identical lambdas produce the *same* results when called from the sheet, and *different* results when called from another Lambda. The different result is an old version of the first lambda which has subsequently been edited.
Any workaround would be greatly appreciated - it's currently impossible to know which lambdas in a codebase are going to produce incorrect results, and unfortunately I now have thousands of lines of code in AFE.
Results:
| =Reconcile.Later() | 0 |
| =Reconcile.LaterTest() | -12836.69 |
| =Reconcile.Later2() | 0 |
| =Reconcile.Later2Test() | 0 |
AFE Code:
// Total amount of reconciliation amounts in the reconcile table that are
// later than the current statement.
Later = LAMBDA(
LET(
ThisAccount, Reconciliations[Account] = Account(),
LaterRecs, (Reconciliations[Statement Date] > StatementDate()),
ThisLLC, Reconciliations[LLC] = LLC(),
Include,ThisAccount * ThisLLC * LaterRecs * (Reconciliations[Completed] = "Y"),
End, SUM(FILTER(Reconciliations[End Balance], Include, 0)),
Start, SUM(FILTER(Reconciliations[Start Balance], Include, 0)),
End - Start
)
);
// Total amount of reconciliation amounts in the reconcile table that are
// later than the current statement.
Later2 = LAMBDA(
LET(
ThisAccount, Reconciliations[Account] = Account(),
LaterRecs, (Reconciliations[Statement Date] > StatementDate()),
ThisLLC, Reconciliations[LLC] = LLC(),
Include,ThisAccount * ThisLLC * LaterRecs * (Reconciliations[Completed] = "Y"),
End, SUM(FILTER(Reconciliations[End Balance], Include, 0)),
Start, SUM(FILTER(Reconciliations[Start Balance], Include, 0)),
End - Start
)
);
LaterTest = LAMBDA(Later());
Later2Test = LAMBDA(Later2());
