Get Count of Change of Statuses Overtime With Snapshot Data

[ray67052]

Greetings,

I have a dataset that is a snapshot of each day and I am trying to get a count of the number of times a work item Failed Test. I'm struggling with getting Power BI to count the number of times it changed, all I can seem to achieve is either a total count of the number of times it has appeared in the dataset or a unique count of the failed test status. I've tried various solutions to posts on the forums to no avail 😞

Here's my dataset:

Work Item ID	Title	Date	State
1	ABC	01/01/2022	New
1	ABC	01/02/2022	Ready For Test
1	ABC	01/03/2022	Failed Test
1	ABC	01/04/2022	Ready For Test
1	ABC	01/05/2022	Failed Test
1	ABC	01/06/2022	Failed Test
2	DEF	01/01/2022	New
2	DEF	01/02/2022	Ready For Test
2	DEF	01/03/2022	Failed Test
2	DEF	01/04/2022	Ready For Test
2	DEF	01/05/2022	Closed
3	XYZ	01/06/2022	Closed
3	XYZ	01/01/2022	New
3	XYZ	01/02/2022	Ready For Test
3	XYZ	01/03/2022	Closed
3	XYZ	01/04/2022	Closed
3	XYZ	01/05/2022	Closed
3	XYZ	01/06/2022	Closed

What I am trying to achieve is the following:

Work Item ID	Title	No. Times Failed Test
1	ABC	2
2	DEF	1
3	XYZ	0

Can anyone suggest the best way to achieve this either by a measure or a column where I can show the number of times the work item state entered Failed Test, please?

Thanks for your help in advance

[FreemanZ · Accepted solution]

Hi @ray67052 
you can create a column with this:

StateChange =

VAR CurrentDate = TableName[Date]

RETURN

    CALCULATETABLE(

        VALUES(TableName[State]),

        TableName[Date] = CurrentDate-1,

        ALLEXCEPT(TableName, TableName[ID], TableName[Title])

    )

&"->"&TableName[State]

then create a SumTable with this:

SumTable =

ADDCOLUMNS(    

   SUMMARIZE(TableName, TableName[ID], TableName[Title]),

    "No. Timies Failed Test",

    CALCULATE(

        COUNTROWS(TableName),

        TableName[StateChange] in {"New->Failed Test", "Ready For Test->Failed Test" }

    )

)

i tried and it worked like this:

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

No. Failed Test measure: =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            Data,
            "@prevstate",
                MAXX (
                    FILTER (
                        Data,
                        Data[Work Item ID] = EARLIER ( Data[Work Item ID] )
                            && Data[Title] = EARLIER ( Data[Title] )
                            && Data[Date]
                                = EARLIER ( Data[Date] ) - 1
                    ),
                    Data[State]
                )
        ),
        Data[State] = "Failed Test"
            && [@prevstate] <> "Failed Test"
    )
) + 0