I am working with accounts receivable forecast report. I have the following script in my pivot chart expression:
If((#InvoiceDate+[Due date])>=MonthEndDate And [Period in Month]<=Month,
The idea of this expression is that it would be looking which sales has been invoiced but not paid yet in the month end. This works very nicely as long as invoices would be paid within next month. However, quite often the case is that payment term is 45 days or even 60 days from the invoice date, which means that these sales should be seen not only in the "sales month" but also in the next month end. My script can't handle these cases.
There is invoice of 1000 eur in 25.5.2013 with payment term 45 days, so invoice will be paid in the July 9th 2013. This should be seen as accounts receivables both in May and in June.With my formula it can be only seen as accounts receivables in May, but not in June anymore. Any ideas how to change the script to handle this issue?
If you'd like some help with this I'd suggest you provide some example actual data to work with. It's difficult to understand your question without this. (For example what is in MonthEndDate / [Period in Month] - these are calculated fields, it's not obvious (well to me anyway).