Create flag based on any value from first column and filter on date column

[Claudem]

Hello all, 

I'm not sure to know how to describe properly my need but I'll try to be clear with a specific example:

part	valid from	flag
A	24/02/2024	N
A	25/12/2023	Y
A	26/11/2023	N
B	24/02/2024	N
B	25/12/2023	Y
B	26/11/2023	N
C	24/02/2024	N
C	25/12/2023	Y
C	26/11/2023	N

Here is the final result. I want to create the flag column based on part and valid from column.
I want to flag to Y this new column the max(valid from) before 01/01/2024 for each part.
Do you guys know how to achieve a such dax formula ?

[Claudem]

ll, thanks for your help. The solutions looks nice but seems to not work in my case. I'm trying to do it on a semantic model connected in direct query. I have a cirular depandancy error when I'm trying the column, and for the measure it seems to duplicate my values with date from other part. The data are not ordered as in the example also.
Any other suggestions ?

[Anonymous]

Hi @Claudem 

Thanks for the reply from fahadqadir3 .

You can also try the following:

Measure:

FlagM = 
VAR _date = DATE(2024, 1, 1)
VAR _maxDate = CALCULATE(MAX([valid from]), FILTER(ALLEXCEPT('Table', 'Table'[part]), [valid from] < _date))
RETURN
IF(MAX('Table'[valid from]) = _maxDate, "Y", "N")

Column:

FlagC = 
VAR _date = DATE(2024, 1, 1)
VAR _maxDate = CALCULATE(MAX([valid from]), FILTER(ALLEXCEPT('Table', 'Table'[part]), [valid from] < _date))
RETURN
IF('Table'[valid from] = _maxDate, "Y", "N")

Output:

Best Regards,
Yulia Xu

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

[fahadqadir3 · Accepted solution]

@Claudem Review the following screenshot and  Column and Measure. 

Flag Measure = 

VAR MaxValidFromBefore2024 =
    CALCULATE(
        MAX('Table'[valid from]),

        FILTER(
            ALL('Table'),

            'Table'[part] = MAX('Table'[part]) &&
            'Table'[valid from] < DATE(2024, 1, 1)
        )

    )
RETURN
IF(

    MAX('Table'[valid from]) = MaxValidFromBefore2024,
    "Y",
    "N"
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!