DAX (add column) identify the max date of grouping

[mlovejoy]

Hello community,

My problematic is straight forward I need to add DAX column. I have cirteria x, y and z. I can identify duplicate with this formula:

Duplicates count =

var x= Criteria
return
COUNTROWS(FILTER(Table,criteria = x)) this give me the number of duplicates (grouping).

Then for every duplicates I need to put a 1 to the most recent date of the grouping and a 0 to the oldest.

Is that even possible to do in DAX add Column?

Thanks for your help,

Mark Lovejoy

[tamerj1]

Hi @mlovejoy 

please try

Result =
MAXX (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Criteria] ) ),
'Table'[Date]
)

[TomasAndersson · Accepted solution]

Hi!

You should be able to do something like this:

Most recent date = 
VAR __IsDuplicate = //Checks if row has a duplicate. If all rows has duplicates, this is not needed.
    If('Table'[Duplicates]>0,TRUE(),FALSE())
    
VAR __MaxDate =  //Finds the most recent (MAX()) date for current criteria
    CALCULATE(
        max('Table'[Date]),
        FILTER(all('Table'),'Table'[Criteria] = EARLIER('Table'[Criteria])
        )
    )
return
if(__IsDuplicate,   //If current row is a duplicate
    if('Table'[Date] = __MaxDate,  //And current row date is the most recent date for this criteria
        1, //return 1
        0  //else return 0
    ),
    0 else return 0
)

Hope this helps!