Need help with returning the maximumum date in a subset of data created with variables

[tcburge3]

Hello,

I am having trouble returning the maximum date in a range when using variables to filter my data.

I have this column ("[Column]") in my table that has some duplicate values. I want to grab the maximum (latest) date in [created_at_stg3] in all instances where my [column] is duplicated.

I have already created this [duplicate] column with dax, using variables, that correctly identifies rows where [column] is duplicated. 

Here is my [Duplicates] column. This column is working fine. (text format of this column is pasted at the end of this post).

The column I need help with is [max duplicate date]. I would like to return the largest/latest [created_at_stg3] value for all instances where [column] is duplicated (this is also indicated by the [Duplicates] column). Currently, This is my formula that returns the [created_at_stg3] value for each row that is duplicated, but I want to return the maximum [created_at_stg3] value for each row where [column] is duplicated. I have highlighted the value that I want to be provided by this column in the snip below. I would like to return BLANK() for any rows where [column] is not duplicated, but that is not entirely necessary.

As you can see, I want the highlighted date under [created_at_stg3] to be returned under [max duplicate date], for both yellow and green.

Eventually, I will use the following IF statement to filter out rows that do not have the maximum [created_at_stg3] for any rows where [column] is duplicated. 

Duplicates to be filtered out =

IF(AND([duplicates]=”Duplicate”,[Max Duplicate Date]<>[created_st_stg3]),1,0)

But first, I need help with returning this max [created_at_stg3] for rows where [column] is duplicated.

Please let me know if you have any thoughts or solutions, and of course I am happy to answer any questions. Your time is appreciated, thank you!

Here are the text formats of my 2 columns mentioned here. My table name is 'stg1_2_3vw1_dev'.

1.

[Duplicates] =

VAR varCurrentValue = 'stg1_2_3_vw1_dev[Column]

VAR varInstances =

    COUNTROWS(

        FILTER(

            'stg1_2_3_vw1_dev’,

            'stg1_2_3_vw1_dev’[Column] = varCurrentValue

        )

    )

var Result =

    IF(

        varInstances > 1,

        "Duplicate",

        "Unique"

    )

RETURN

    Result

2.

[max duplicate date] =

VAR varCurrentValue = 'stg1_2_3_vw1_dev'[Column]

VAR varInstances =

    COUNTROWS(

        FILTER(

            'stg1_2_3_vw1_dev',

            'stg1_2_3_vw1_dev'[Column] = varCurrentValue

        )

    )

var Result =

    IF(

        varInstances > 1,

        ([created_at_stg3]),

        BLANK()

    )

RETURN

    Result

[v-jiewu-msft · Accepted solution]

Hi @tcburge3 ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the measure to return the largest date for instances where column is duplicated.

max Duplicates date = 
VAR varCurrentValue = 'stg1_2_3vw1_dev'[Column]
VAR varInstances =
    COUNTROWS(
        FILTER(
            'stg1_2_3vw1_dev',
            'stg1_2_3vw1_dev'[Column] = varCurrentValue
        )
    )
VAR varMaxDate = CALCULATE(
    MAX('stg1_2_3vw1_dev'[created_at_stg3]),
    FILTER(
        'stg1_2_3vw1_dev',
        'stg1_2_3vw1_dev'[Column] = varCurrentValue
    )
)
var Result =
    IF(
        varInstances > 1,
        varMaxDate,
        BLANK()
    )
Return
    Result

3.Drag the measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[tcburge3]

Thank you! This is just what I'm looking for. Accepted it as the solution. Thank you so much for your help.

[tcburge3]

just to reiterate. [max duplicate date] is the column I need help with. In the highlighted snip, I want the yellow value under [created_at_stg3] to be returned for both of the highlighted rows under [max duplicate date]. And the same for the green highlighted values. Thank you so much! Sorry if I am a bit of an over-explainer