topic Re: Outdated product - recertification, need help with DAX in DAX Commands and Tips

Outdated product - recertification, need help with DAX

Anonymous — Mon, 29 Aug 2022 06:30:28 GMT

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?

Re: Outdated product - recertification, need help with DAX

amitchandak — Mon, 29 Aug 2022 04:37:51 GMT

Anonymous , 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.

Re: Outdated product - recertification, need help with DAX

Anonymous — Mon, 29 Aug 2022 05:40:40 GMT

Hello, This is sample output in table format:

Column Product Name - Format: Text

Yearly_review - Format: Date

Re: Outdated product - recertification, need help with DAX

Anonymous — Wed, 31 Aug 2022 09:15:23 GMT

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

Re: Outdated product - recertification, need help with DAX

Anonymous — Sun, 04 Sep 2022 18:40:39 GMT

Thank you very much Anonymous !

Re: Outdated product - recertification, need help with DAX

Anonymous — Mon, 05 Sep 2022 06:29:00 GMT

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