Column Filtering within Summarize

[Syndicate_Admin]

Good day I am working with a table created called OTIF Table to calculate fulfillments in complete deliveries and on time of purchase orders, I have created the OTIF Table with summarize where it brings me the PONumber Number, QuantityOrdered, QuantityReceived, DateReceived, EstShipDate but within this table I want to include a filter for the QuantityReceived column where I add the items received, only if the date of receipt of such items is less than the DateReceived date.

Annex table creation formula

OTIF Table = SUMMARIZE(PurchaseOrderDetail,PurchaseOrderDetail[PONumber],
"Qty Order",sum(PurchaseOrderDetail[QuantityOrdered]),
"Qty Received",sum(PurchaseOrderDetail[QuantityReceived]),
"Date Received",Max(PurchaseOrderDetail[DateReceived].[ Date]),
"ETA",Max(PurchaseOrder[EstShipDate].[ Date]))

[amitchandak]

@Syndicate_Admin , Try like

Change it like

"Qty Received",calculate(sum(PurchaseOrderDetail[QuantityReceived]),filter(PurchaseOrderDetail,PurchaseOrderDetail[DateReceived]<PurchaseOrder[EstShipDate]))

or after summarize

filter(

SUMMARIZE(PurchaseOrderDetail,PurchaseOrderDetail[PONumber],
"Qty Order",sum(PurchaseOrderDetail[QuantityOrdered]),
"Qty Received",sum(PurchaseOrderDetail[QuantityReceived]),
"Date Received",Max(PurchaseOrderDetail[DateReceived].[ Date]),
"ETA",Max(PurchaseOrder[EstShipDate].[ Date])), [Date Received]<[ETA] )

[Syndicate_Admin]

Syndicate Admin

I do not find the proposal when performing the filter after annotating the table PurchaseOrderDetail does not let me put the condition <= PurchaseOrder EStShipDate

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can update the formula of calculated table as below, please find the details in the attachment.

OTIF Table = 
SUMMARIZE (
    FILTER (
        'PurchaseOrderDetail',
        'PurchaseOrderDetail'[DateReceived]
            <= CALCULATE (
                MAX ( 'PurchaseOrder'[EstShipDate] ),
                FILTER (
                    'PurchaseOrder',
                    'PurchaseOrder'[PONumber] = EARLIER ( 'PurchaseOrderDetail'[PONumber] )
                )
            )
    ),
    PurchaseOrderDetail[PONumber],
    "Qty Order", SUM ( PurchaseOrderDetail[QuantityOrdered] ),
    "Qty Received", SUM ( PurchaseOrderDetail[QuantityReceived] ),
    "Date Received", MAX ( PurchaseOrderDetail[DateReceived] ),
    "ETA", MAX ( PurchaseOrder[EstShipDate] )
)

PurchaseOrderDetail and PurchaseOrder table

Summarize table OTIF Table

Best Regards