Forum Discussion
Outdated product - recertification, need help with DAX
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?
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
5 Replies
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.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
