Forum Discussion
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
Super UserAnonymous , 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.
Hello, This is sample output in table format:
Column Product Name - Format: Text
Yearly_review - Format: Date
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
Thank you very much Anonymous !