combination of ID and date for IF function

[alevandenes]

Hi community,

i want to add a column that states "consider" when the date is MAX(date) for a specific ID.

for the dates that are not MAX, it would state "dont consider"

i manage to go until here, but i do not know how to count for the ID as well:

Consider = if('Project1'[Date]=MAX('Project1'[Date]), "consider", "dont consider")

thanks a lot in advance,
Alessandra

[Anonymous]

Hi @alevandenes ,

Step 1: Assuming your dataset is like below

Step 2: Create a calculated column

MaxDate = 
IF (
    CALCULATE ( MAX ( Sheet1[Date] ), ALLEXCEPT ( Sheet1, Sheet1[ID] ) ) = Sheet1[Date],
    "Consider",
    "Dont Consider"
)

Step 3: End

BR

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your table looks like, but could you please try the below calculated column formula?

Consider CC =
VAR _maxdate =
    MAXX (
        FILTER ( 'Project1', 'Project1'[ID] = EARLIER ( 'Project1'[ID] ) ),
        'Project1'[Date]
    )
RETURN
    IF ( 'Project1'[Date] = _maxdate, "Consider", "Dont consider" )