Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Column Filtering within Summarize

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]))

  • Anonymous's avatar
    Anonymous
    3 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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