cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Power BI DAX occurence per part number

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.

1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Community Support

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

New Member

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! ✌️

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors