MAX DATE formula

[ThomasJB2004]

Hi, 

This is the DAX formula i have at the moment, this part works but with the data i work with some rows will have the same dates but i can work it out using an ID. How can i create this DAX to first look for the newest date and if that has 2 or more of the same date look for the Highest ID linked to the date and only show me one Row of each 'person'

Stopberichten Nieuwste =

VAR _maxdate =

    CALCULATE (

        MAX ( 'leveringsinformatie stop'[Ontvangstdatum stopbericht] ),

        FILTER ( 'leveringsinformatie stop', 'leveringsinformatie stop'[Toewijzingsnummer] = EARLIER ( 'leveringsinformatie stop'[Toewijzingsnummer] ) )

    )

RETURN

    IF ( 'leveringsinformatie stop'[Ontvangstdatum stopbericht] = _maxdate, TRUE(),  FALSE() )

[Anonymous]

Hi @ThomasJB2004 ,

Based on your problems, I think you can create a DAX formula that considers both the newest date and the highest ID for each person.

You can calculate the maximum date for each person using the MAX function. This ensures that we get the most recent date associated with each person. Next, you can filter the rows to keep only those with the maximum date. Then use the FILTER  function to achieve this.  If there are multiple rows with the same maximum date,  choose the one with the highest ID. Then use the EARLIERfunction to compare the current row’s date with the maximum date.

Here is the updated DAX code.

Stopberichten Nieuwste =
VAR _maxdate =
    CALCULATE (
        MAX ( 'leveringsinformatie stop'[Ontvangstdatum stopbericht] ),
        FILTER (
            'leveringsinformatie stop',
            'leveringsinformatie stop'[Toewijzingsnummer]
                = EARLIER ( 'leveringsinformatie stop'[Toewijzingsnummer] )
        )
    )
RETURN
    IF (
        'leveringsinformatie stop'[Ontvangstdatum stopbericht] = _maxdate
            && 'leveringsinformatie stop'[Toewijzingsnummer]
                = CALCULATE (
                    MAX ( 'leveringsinformatie stop'[Toewijzingsnummer] ),
                    FILTER (
                        'leveringsinformatie stop',
                        'leveringsinformatie stop'[Ontvangstdatum stopbericht] = _maxdate
                    )
                ),
        TRUE (),
        FALSE ()
    )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[123abc]

To achieve your goal of selecting the newest date for each person, with a tiebreaker based on the highest ID in case of identical dates, you can adjust your DAX formula. You will need to first find the maximum date for each person, and then find the highest ID associated with that maximum date. Here's how you can modify your DAX formula:

Stopberichten Nieuwste =
VAR _maxdate =
CALCULATE (
MAX ( 'leveringsinformatie stop'[Ontvangstdatum stopbericht] ),
ALLEXCEPT('leveringsinformatie stop', 'leveringsinformatie stop'[Toewijzingsnummer])
)
VAR _maxID =
CALCULATE (
MAX ( 'leveringsinformatie stop'[Toewijzingsnummer] ),
FILTER (
ALLSELECTED('leveringsinformatie stop'),
'leveringsinformatie stop'[Ontvangstdatum stopbericht] = _maxdate
)
)
RETURN
IF (
'leveringsinformatie stop'[Ontvangstdatum stopbericht] = _maxdate &&
'leveringsinformatie stop'[Toewijzingsnummer] = _maxID,
TRUE(),
FALSE()
)

Explanation:

1. _maxdate: This variable calculates the maximum date for each person (Toewijzingsnummer) using ALLEXCEPT. It removes all context filters from the table except for the Toewijzingsnummer column.

2. _maxID: This variable calculates the maximum ID associated with the maximum date. It uses FILTER to consider only the rows with the maximum date calculated earlier.

3. The RETURN part checks if the current row's date matches the maximum date for the person and if the ID matches the maximum ID associated with that date. If both conditions are true, it returns TRUE, indicating that this row has the latest date for the person, otherwise, it returns FALSE.

This formula should give you the desired behavior of selecting the newest date and highest ID for each person in your dataset.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.