Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors