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
Spudduk
Frequent Visitor

DAX to Select Last Date on 2 Matching Criteria

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 DateMachineDate_TimeLast Record
26/06/2021A125/06/2021 09:00 
26/06/2021A125/06/2021 12:40 
26/06/2021A125/06/2021 17:10YES
26/06/2021A225/06/2021 19:50YES
26/06/2021A225/06/2021 09:00 
27/06/2021A126/06/2021 14:50YES
27/06/2021A126/06/2021 08:45 
27/06/2021A126/06/2021 12:51 
27/06/2021A226/06/2021 15:45YES
27/06/2021A226/06/2021 08:43 
28/06/2021A127/06/2021 19:56YES
28/06/2021A127/06/2021 07:45 
28/06/2021A227/06/2021 07:56YES
28/06/2021A227/06/2021 06:45 
28/06/2021A326/06/2021 23:45 
28/06/2021A327/06/2021 19:45YES
28/06/2021A327/06/2021 08:45 
4 ACCEPTED SOLUTIONS
CNENFRNL
Community Champion
Community Champion

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

Screenshot 2021-06-26 221300.png

 

Bonus solution with Power Query

Screenshot 2021-06-26 220548.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

FrankAT
Community Champion
Community Champion

Hi @Spudduk ,

you can do it like this:

 

26-06-_2021_23-50-12.png

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)

View solution in original post

Jihwan_Kim
Super User
Super User

Picture1.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

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 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Jihwan_Kim
Super User
Super User

Picture1.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


FrankAT
Community Champion
Community Champion

Hi @Spudduk ,

you can do it like this:

 

26-06-_2021_23-50-12.png

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
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

CNENFRNL
Community Champion
Community Champion

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

Screenshot 2021-06-26 221300.png

 

Bonus solution with Power Query

Screenshot 2021-06-26 220548.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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.