cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

MAX Date per Material where Measure > 0

Hi, what I am trying to do is to get the maximum date by Material. I'm using datasets, tables with attributes, and tables with measures. 

 

DAX:

EVALUATE
SUMMARIZECOLUMNS (
    'Material'[MAT WRIN0],
    'Delivery Date'[DLVD Date],
    'Purchase Order'[PO Number],

    FILTER (
        VALUES ( 'Distributor'[DIST Country] ),
        ( 'Distributor'[DIST Country] = "Romania" )
    ),
    FILTER (
        VALUES ( 'Purchase Order'[PO Status Description] ),
        ( 'Purchase Order'[PO Status Description] = "Purchase order APPROVED" )
    ),

    "Purchase Order To Be Received Quantity", [Purchase Order To Be Received Quantity]
    )
ORDER BY 'Delivery Date'[DLVD Date]

 

Here is the data extract, and the final result should be like WRIN0 and Max DLVD Date (just one line per WRIN0).

NewUserHI21_0-1654768075161.png

 

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Anonymous 

please use

 

EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        'Material'[MAT WRIN0],
        'Delivery Date'[DLVD Date],
        'Purchase Order'[PO Number],
        FILTER (
            VALUES ( 'Distributor'[DIST Country] ),
            ( 'Distributor'[DIST Country] = "Romania" )
        ),
        FILTER (
            VALUES ( 'Purchase Order'[PO Status Description] ),
            ( 'Purchase Order'[PO Status Description] = "Purchase order APPROVED" )
        ),
        "Purchase Order To Be Received Quantity", [Purchase Order To Be Received Quantity]
    ),
    'Delivery Date'[DLVD Date]
        = CALCULATE (
            MAX ( 'Delivery Date'[DLVD Date] ),
            ALLEXCEPT ( 'Material', 'Material'[MAT WRIN0] )
        )
)
ORDER BY 'Delivery Date'[DLVD Date]

 

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

Hi @Anonymous 

please use

 

EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        'Material'[MAT WRIN0],
        'Delivery Date'[DLVD Date],
        'Purchase Order'[PO Number],
        FILTER (
            VALUES ( 'Distributor'[DIST Country] ),
            ( 'Distributor'[DIST Country] = "Romania" )
        ),
        FILTER (
            VALUES ( 'Purchase Order'[PO Status Description] ),
            ( 'Purchase Order'[PO Status Description] = "Purchase order APPROVED" )
        ),
        "Purchase Order To Be Received Quantity", [Purchase Order To Be Received Quantity]
    ),
    'Delivery Date'[DLVD Date]
        = CALCULATE (
            MAX ( 'Delivery Date'[DLVD Date] ),
            ALLEXCEPT ( 'Material', 'Material'[MAT WRIN0] )
        )
)
ORDER BY 'Delivery Date'[DLVD Date]

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.