Profit Report?

I'd like some help on creating a formula to determine daily profit. (Sales - Cost = Profit) I'd like this formula to work in a single cell. We're using NAV 5.0 SP1.

For Sales, I SUM the AMOUNT of the Sales Invoice Line, using a date filter on the Sales Header.
The AMOUNT equals the quantity X price so my sales numbers are always correct.

My dilemma with determining COST is that there isn't a TOTAL COST on the sales line. To determine the cost of the line I must multiply quantity X unit cost, per line, then add the sum of all those for the specified date range.

I've posted my Sales formula. We have 7 locations and I must filter the Department Code on the sales header (who wrote the order) and the Location Code on the sales line (where it was invoiced). If I could determine the COST of the formula below, I could then determine the profit.

=SUM(NL("Sum","Sales Invoice Line","Amount","Location Code",$B7,"Type","Item","Document No.",NL("Filter","Sales Invoice Header","No.","Posting Date",$D$2,"Shortcut Dimension 1 Code",D$6))-(NL("Sum","Sales Cr.Memo Line","Amount","Location Code",$B7,"Type","Item","Document No.",NL("Filter","Sales Cr.Memo Header","No.","Posting Date",$D$2,"Shortcut Dimension 1 Code",D$6))))

Thank you for your help!


Please sign in to leave a comment.