I'm trying to calculate the last invoice date based on a version date (snapshot date).
In order to accomplish this I have loaded both the snapshot dates as well as the detailed inventory movements as seperate tables.
The next step is to calculate the last invoice date at each snapshot date and this is where i got stuck.
I created a cartesian product of the version dates and all movements.
In a next step I'm trying to calculate the last invoice date given the version date as follows:
if(Date#(InvoiceDate) <= Date#(VersionDate), max(IssueDate),'Unknown') as LastInvoiceDate
group by VersionDate, SSP, Material;
The result is en 'Error in expression'. Can anyone what is wrong in this statement?
Thank you very much in advance,
ps: The difficulty here is that the last invoice date for a certain material at a certain warehouse could be several months earlier than the month of the snapshot but then that still remains the last invoice date.