Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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/...
Need Help
  • fahadqadir3's avatar
    fahadqadir3
    1 year ago

    Anonymous 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!!

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Anonymous 

 

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.