Outdated product - recertification, need help with DAX

Hi Anonymous ,

According to your description, You want to count the number of [Product_Name] fields in three different cases based on the comparison of the [Yearly_Review] field with TODAY().. Right?

Here are the steps you can follow：

(1)This is my test data:

(2)We can create a calculated column : “Max Date” (If there is a duplication, we need to find the maximum review date for the product)

Max Date =
VAR _current_name = 'Table'[Product_Name]
VAR _p_table =
    FILTER ( 'Table', 'Table'[Product_Name] = _current_name )
RETURN
    MAXX ( _p_table, [Yearly_Review] )

(3)We can create three measures to meet your need now:

ALL Product =
VAR _table =
    SUMMARIZE ( 'Table', 'Table'[Product_Name], 'Table'[Max Date] )
RETURN
    COUNTX ( FILTER ( _table, [Max Date] < TODAY () ), [Product_Name] )

Less than 6 months =
VAR _table =
    SUMMARIZE ( 'Table', 'Table'[Product_Name], 'Table'[Max Date] )
VAR _filter =
    FILTER (
        _table,
        DATEDIFF ( [Max Date], TODAY (), MONTH ) <= 6
            && DATEDIFF ( [Max Date], TODAY (), MONTH ) >= 0
            && [Max Date] < TODAY ()
    )
RETURN
    COUNTROWS ( _filter )

More than 6 months =
VAR _table =
    SUMMARIZE ( 'Table', 'Table'[Product_Name], 'Table'[Max Date] )
VAR _filter =
    FILTER (
        _table,
        [Max Date] < TODAY ()
            && DATEDIFF ( [Max Date], TODAY (), MONTH ) > 6
    )
RETURN
    COUNTROWS ( _filter )

(4)We can put these measures in the card to test:

If this method can't meet your requirement, can you provide some special input and output examples? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards