Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dgadzinski
Helper I
Helper I

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?

1 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:

yingyinr_0-1661937038748.png

 

(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] )

yingyinr_1-1661937038751.png

 

(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:

yingyinr_3-1661937300575.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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.

Hello, This is sample output in table format: 

dgadzinski_0-1661751428340.png

Column Product Name - Format: Text 

Yearly_review - Format: Date

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:

yingyinr_0-1661937038748.png

 

(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] )

yingyinr_1-1661937038751.png

 

(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:

yingyinr_3-1661937300575.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 ?

Thank you very much @v-yiruan-msft  !

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.