How to obtain the last record (date type) and not categorize the Calc Field as "Agregate"

Hi,

I need to create a report that gives me a KPI of the percentage of Deliveries on Time according to a certain period.

DIFOT – Delivery In Full On Time

Sales Order

Sales Order Item

Planned Delivery Date

Real Delivery Date

(Invoice)

DIFOT

50001

1

01/25/2017

01/25/2017

50%

50001

2

01/25/2017

01/27/2017

50001

3

01/30/2017

01/30/2017

100%

DIFOT for S.O. 50001 should be 75%

The Report takes in consideration the lines with same Planned Delivery Date as one consolidated delivery and ONLY Sales Order completely delivered (balance or pending deliveries are zero).

In case there are partial deliveries for an Item, the report should take in consideration the last invoicing date for that particular item.

Real Tableau example:

In this example, I’m using data from 2 tables:

•NF_Item (Invoice)

•PV_Item (Sales Order)

In order to calculate if the product was delivered in full on time, I had to create a Calculated Field to determine the last invoiced date for a particular S.O. Item.:

Last Invoice

(this is required to get the date when the last the last invoice was generated to the specific Sales, avoiding parcial invoicing)

When trying to create an additional Calculated Field for the DIFOT, I got an inconsistency:

DIFOT

(calculation that gives me 1 when the Sales was invoiced before the planned date and 0 when the Sales was invoiced after the planned date)

PROBLEM: As calculated field “Last Invoice” is an agregation, it cannot be used to compare with a non-agregation field.

How to fix this issue? Or how to obtain the last invoiced date not as Agregation type?

Thank you,

Thiago