Calculating the no. tripled values in 12 weeks

[Anonymous]

Hello

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

Asset ID	Date of 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'm trying to use DAX to create a new variable/measure that calculates the number of repeat errors. A repeat error would be defined as:

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

- ... within a12-week period

Therefore, when examining the previous dataset, the AK-0013 asset made a repeat error, as it failed three times in a 12-week period, but the AK-0001 asset has zero repeat errors even if it failed three times separately, because the errors occurred over a period longer than 12 weeks.

I have used DAX several times before for different measurements and have always searched Google intensely before asking in the community (depending on keywords). However, this is the first time I am perplexed and will gladly welcome you to any help and advice on how to create this measure! If you have any questions or need further elaboration please ask.

Thanks a lot

apb_123

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create a measure with the below formula:

FailCount = 
VAR countofFail =
    CALCULATE (
        DISTINCTCOUNT ( 'Assets'[Date of entry] ),
        FILTER ( 'Assets', 'Assets'[Asset ID] = MAX ( 'Assets'[Asset ID] ) )
    )
RETURN
    IF (
        DATEDIFF (
            MIN ( 'Assets'[Date of entry] ),
            MAX ( 'Assets'[Date of entry] ),
            WEEK
        ) <= 12
            && countofFail >= 3,
        countofFail,
        BLANK ()
    )

Best Regards

Rena

[jstorm]

You need to calculate your period then do a DISTINCTCOUNT() of Asset ID in that period.  If the count is greater than 2, then record the amount over 2 as # of repeats.

VAR count = 0
VAR currentDate = SELECTEDVALUE( 'Assets'[Date] )
VAR repeats =

CALCULATE(
    DISTINCTCOUNT( [Asset ID] ),
    'DatesTable'[Date] > DATEADD( currentDate, -3, WEEK ) &&
    'DatesTable'[Date] <= CurrentDate
) - 2

RETURN

IF(
    repeats > 2,
    count + repeats,
    count + 0
)

[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