Outdated product - recertification, need help with DAX

[dgadzinski]

Hello All, 

I need help with DAX. I have to calculate column "Outdated Product" - this is number od product with overdue recertification date. I need 3 objectives: 
1) All Product are recertified. 
2) At least one product recertification is overdue by less than 6 months. 
3) At least one product recertification is overdue by more than 6 months. 

I have 1 Table "Product" and 2 columns: 
- Yearly_Review
- Product_Name

Could you help me, please?

[amitchandak]

@dgadzinski , You can have a new column like, that can help

datediff([recertification date], today(), Month) +1

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[dgadzinski]

Hello, This is sample output in table format: 

Column Product Name - Format: Text 

Yearly_review - Format: Date

[v-yiruan-msft · Accepted solution]

Hi @dgadzinski ,

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

[dgadzinski]

If I would like to return text in the table "Less than 6 month" and "More than 6 month" instead of 1, what steps should i follow? Could you help me @v-yiruan-msft ?

[dgadzinski]

Thank you very much @v-yiruan-msft  !