DAX to Select Last Date on 2 Matching Criteria

[Spudduk]

Hi All,

Below is a sample stand alone table that I have on BI. 

The data comes from an Excel file so gets updated many times during the day.

I am trying to add a collumn (LAST RECORD)  that identifies that latest time where the Sales Date and Machine Match.

So in the example below of the 3 records with Sales Date 26/06/2021 and Machine A1 what is the latest date/time ?

Any help or guidence appreciated as I have got so far with VAR statements as I can and still just cant crack it.

Thanks in anticipation.

Format of fields are as follows.

SALES DATE is  Date format, Machine = Text and Date_Time = Date/Time format.

Sales Date	Machine	Date_Time	Last Record
26/06/2021	A1	25/06/2021 09:00
26/06/2021	A1	25/06/2021 12:40
26/06/2021	A1	25/06/2021 17:10	YES
26/06/2021	A2	25/06/2021 19:50	YES
26/06/2021	A2	25/06/2021 09:00
27/06/2021	A1	26/06/2021 14:50	YES
27/06/2021	A1	26/06/2021 08:45
27/06/2021	A1	26/06/2021 12:51
27/06/2021	A2	26/06/2021 15:45	YES
27/06/2021	A2	26/06/2021 08:43
28/06/2021	A1	27/06/2021 19:56	YES
28/06/2021	A1	27/06/2021 07:45
28/06/2021	A2	27/06/2021 07:56	YES
28/06/2021	A2	27/06/2021 06:45
28/06/2021	A3	26/06/2021 23:45
28/06/2021	A3	27/06/2021 19:45	YES
28/06/2021	A3	27/06/2021 08:45

[CNENFRNL · Accepted solution]

Last_Record_CC = 
VAR __mch = SALES[Machine]
VAR __dt = SALES[Sales Date]
RETURN
    IF(
        SALES[Date_Time]
            = MAXX(
                FILTER( SALES, SALES[Machine] = __mch && SALES[Sales Date] = __dt ),
                SALES[Date_Time]
            ),
        "YES"
    )

Bonus solution with Power Query

[parry2k · Accepted solution]

@Spudduk here is a DAX to add a column:

Last Col = 
IF ( 
    'Table'[Date_Time] = CALCULATE ( MAX ( 'Table'[Date_Time] ), ALLEXCEPT ( 'Table', 'Table'[Machine], 'Table'[Sales Date] ) ), 
    "Yes" 
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[FrankAT · Accepted solution]

Hi @Spudduk ,

you can do it like this:

Last Record = 
VAR _MaxTime =
    CALCULATE (
        MAX ( 'Table'[Date_Time] ),
        ALLEXCEPT ( 'Table', 'Table'[Sales Date], 'Table'[Machine] )
    )
RETURN
    IF ( MIN ( 'Table'[Date_Time] ) = _MaxTime, "Yes", "-" )

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[parry2k]

WOW, never seen so many people answering the same comparatively simple question, in a way it is good, you get to see how other things and solve the problem but thinking from the OP perspective, must be very confusing seeing so many solutions, what to use and what to ignore? 

Good luck @Spudduk 

[Jihwan_Kim · Accepted solution]

Last Record CC =

IF (

'Table'[Date_Time]

= MAXX (

FILTER (

'Table',

'Table'[Sales Date] = EARLIER ( 'Table'[Sales Date] )

&& 'Table'[Machine] = EARLIER ( 'Table'[Machine] )

),

'Table'[Date_Time]

),

"YES"

)

https://www.dropbox.com/s/z8ej04qtjjvgr7n/spudduk.pbix?dl=0 

[FrankAT · Accepted solution]

Hi @Spudduk ,

you can do it like this:

Last Record = 
VAR _MaxTime =
    CALCULATE (
        MAX ( 'Table'[Date_Time] ),
        ALLEXCEPT ( 'Table', 'Table'[Sales Date], 'Table'[Machine] )
    )
RETURN
    IF ( MIN ( 'Table'[Date_Time] ) = _MaxTime, "Yes", "-" )

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[parry2k · Accepted solution]

@Spudduk here is a DAX to add a column:

Last Col = 
IF ( 
    'Table'[Date_Time] = CALCULATE ( MAX ( 'Table'[Date_Time] ), ALLEXCEPT ( 'Table', 'Table'[Machine], 'Table'[Sales Date] ) ), 
    "Yes" 
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[CNENFRNL · Accepted solution]

Last_Record_CC = 
VAR __mch = SALES[Machine]
VAR __dt = SALES[Sales Date]
RETURN
    IF(
        SALES[Date_Time]
            = MAXX(
                FILTER( SALES, SALES[Machine] = __mch && SALES[Sales Date] = __dt ),
                SALES[Date_Time]
            ),
        "YES"
    )

Bonus solution with Power Query