Calculating the no. of triplicate values within a 12 week timeframe

[Anonymous]

Hi there,

I am currently investigating a dataset that contains the following columns below.  The table refers to a number of assets that appear in a dataset if they have been found to have failed upon inspection.

Asset ID	Date of input entry
AK-0001	02-03-2019
AK-0013	05-03-2020
AK-0321	17-03-2020
AK-0001	17-03-2020
AK-0013	21-03-2020
AK-0013	24-04-2020
AK-0001	30-03-2020

I am trying to use DAX to create a new variable/measure that calculates the number of Repeat Failures.  A repeat failure would be defined as:

- The same asset (identified by Asset ID) failing three or more times...

- ...within a12 week period

So, looking at the dataset above, Asset AK-0013 incurred one repeat failure as it failed three times within a 12 week period, but Asset AK-0001 has zero repeat failures even if it failed three separate times, because the failures occurred over a period greater than 12 weeks.

I've used DAX multiple times before for different measures and have always googled intensively before asking on the Community (depending on the keywords).  However this is the first time I'm stumped and will gladly welcome any help and advice on how to create this measure!  If you have any questions or require further elaboration please do ask.

Many thanks,

apb_123

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

Try measures as below:

flag =
VAR _totalrows =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Asset ID] = MAX ( 'Table'[Asset ID] ) )
    )
VAR _mindate =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Asset ID] = MAX ( 'Table'[Asset ID] )
                && 'Table'[Date of entry] < MAX ( 'Table'[Date of entry] )
        ),
        'Table'[Date of entry]
    )
VAR c =
    DATEDIFF ( _mindate, MAX ( 'Table'[Date of entry] ), WEEK )
RETURN
    IF ( _totalrows >= 3 && c < 12, "repeat error", BLANK () )

number of Repeat Failures =
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Asset ID] = MAX ( 'Table'[Asset ID] ) )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Asset ID] = MAX ( 'Table'[Asset ID] )
                && 'Table'[flag] <> BLANK ()
        )
    )
VAR c =
    IF ( a >= 3 && a - b < 2, 1, 0 )
RETURN
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Asset ID] = MAX ( 'Table'[Asset ID] ) ),
        c
    )

The result wil show as below:

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

[amitchandak]

@Anonymous ,

With help of date table and week Rank you can get 12 week data like this

Last 12 week Sales = CALCULATE(Count(Table[Asset ID]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-12 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

You can filter on > 3

Refer, how to deal with the week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184