IF condition on multiple column condition based on latest date

[insandur]

when date is maximum and status is closed return "close" for the shipment for all dates and if max date has "Open" as status return "Open" for all dates for that shipments.
eg:

Shipments	Date	Status	New Status
A	7-Oct	Open	Close
A	8-Oct	OPen	Close
A	9-Oct	Close	Close
B	7-Oct	Open	Open
C	7-Oct	Open	Close
A	6-Oct	Open	Open
B	8-Oct	Open	Open
B	9-Oct	Open	Open
C	8-Oct	Close	Close

[tamerj1 · Accepted solution]

Hi @insandur 

please try

New Status =
VAR CurrentShipmentTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Shipments] ) )
VAR LastRecord =
    TOPN ( 1, CurrentShipmentTable, 'Table'[Date] )
RETURN
    MAXX ( LastRecord, 'Table'[Status] )

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file whether it suits your requirement.

New Status CC =
MAXX (
    FILTER (
        Data,
        Data[Shipments] = EARLIER ( Data[Shipments] )
            && Data[Date]
                = MAXX (
                    GROUPBY (
                        FILTER ( Data, Data[Shipments] = EARLIER ( Data[Shipments] ) ),
                        Data[Shipments],
                        "@maxdate", MAXX ( CURRENTGROUP (), Data[Date] )
                    ),
                    [@maxdate]
                )
    ),
    Data[Status]
)

[insandur · Accepted solution]

This worked, Thank you for quick response

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file whether it suits your requirement.

New Status CC =
MAXX (
    FILTER (
        Data,
        Data[Shipments] = EARLIER ( Data[Shipments] )
            && Data[Date]
                = MAXX (
                    GROUPBY (
                        FILTER ( Data, Data[Shipments] = EARLIER ( Data[Shipments] ) ),
                        Data[Shipments],
                        "@maxdate", MAXX ( CURRENTGROUP (), Data[Date] )
                    ),
                    [@maxdate]
                )
    ),
    Data[Status]
)

[tamerj1 · Accepted solution]

Hi @insandur 

please try

New Status =
VAR CurrentShipmentTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Shipments] ) )
VAR LastRecord =
    TOPN ( 1, CurrentShipmentTable, 'Table'[Date] )
RETURN
    MAXX ( LastRecord, 'Table'[Status] )