Power BI DAX occurence per part number

[KarinaQuino]

I'm working with a dataset in Power BI that includes a table called 'ECN_AFFECTED_ITEMS_V'. This table has columns such as 'Part Number' and 'Release Date'. Each row represents an incident related to a specific part number, and incidents can occur on different dates.

I need to create a DAX formula to analyze incidents within a 6-month period for each part number. Specifically, I want to determine if any part number has multiple incidents that occurred within 6 months of each other and count those occurrences. Additionally, I need to check if any consecutive incidents for each part number meet the condition of being within 6 months of each other. How can I achieve this in Power BI?

As seen in the image, that part number has 8 revisions or occurrences. I want to analyze the distance between each date to check if it is <=6 months. If one meets this criterion, mark it as 1; otherwise, mark it as 0. This should be evaluated between each of the dates.

[v-xinruzhu-msft · Accepted solution]

Hi,

@Ashish_Mathur , thanks for concern about the problem, and i want to offers some information for user to refer to.

hello @KarinaQuino , based on your description, you can refer to the following solution.

Sample data 

1.You can create a rank ciolumn in table

Rank = RANKX(FILTER('Table',[Part Numner]=EARLIER('Table'[Part Numner])),[Release Date],,ASC,Dense)

2.Then create a measure

MEASURE =
VAR _add =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "flag",
            VAR a =
                CALCULATE (
                    MIN ( 'Table'[Release Date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Part Numner] ),
                    'Table'[Rank]
                        = EARLIER ( 'Table'[Rank] ) - 1
                )
            VAR b =
                DATEDIFF ( a, [Release Date], DAY )
            RETURN
                IF ( b <= 180 && b > 0, 1, 0 )
    )
RETURN
    IF (
        SUMX (
            FILTER ( _add, [Part Numner] IN VALUES ( 'Table'[Part Numner] ) ),
            [flag]
        ) > 0,
        1,
        0
    )

Output

Best Regards!

Yolo Zhu

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

[KarinaQuino]

Thank so much!!

This was exactly what I needed, the truth is I had been trying different ways for 3 days, and this is because I don't have much experience in DAX, but this was perfect! ✌️

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result very clearly.  Share data in a format that can be pasted in an MS Excel file.