cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Find items that have gone back in status

Hello there,

I can't quite figure out fix the following.

I have a table with the day-by-day status of PBIs and want to check if items have gone back in status (e.g. from In Progress to New). My idea was to [1] Create a separate table with a ranking per status, [2] create a measure like a count that compares the first date of the max rank against the last date of a rank that is not max, [3] the PBI is marked as 1 if the above applies so that I can determine how often it has occurred within a period or within an area.

My attempt was the following, but it doesn't work properly as the first date seems to just return 1-1-2024 in all cases:

AR_Reverse =
CALCULATE(
COUNT(Test_Table[Date]),
FILTER(Test_Table,
CALCULATE(
FIRSTDATE(Test_Table[Date]),
FILTER(Test_Table, MAX(Test_Table[Rank.Rank])))
<
CALCULATE(
LASTDATE(Test_Table[Date]),
FILTER(Test_Table, Test_Table[Rank.Rank] <> MAX(Latest_Table[Rank.Rank])))
)
)

Sample table:

 Area PBI Date Status Rank Area A 1 1-1-2024 Proposed 1 Area A 1 2-1-2024 In Progress 2 Area A 1 3-1-2024 Completed 3 Area A 2 1-1-2024 Proposed 1 Area A 2 2-1-2024 In Progress 2 Area A 2 3-1-2024 Completed 3 Area B 3 1-1-2024 Completed 3 Area B 3 2-1-2024 In Progress 2 Area B 3 3-1-2024 In Progress 2 Area B 4 1-1-2024 Proposed 1 Area B 4 2-1-2024 Completed 3 Area B 4 3-1-2024 In Progress 2

Expected Result:

 Area Reverse Area A 0 Area B 2
1 ACCEPTED SOLUTION
Community Champion

Hi @PJVisscher89 - Create a calculated column in your sample table shared.

PreviousRank =
VAR CurrentDate = Reverse[Date]
RETURN
CALCULATE(
MAX(Reverse[Rank]),
FILTER(
Reverse,
Reverse[PBI] = EARLIER(Reverse[PBI]) &&
Reverse[Date] < CurrentDate
)
)

Create another calculated column with flag 0,1 in same table.

ReversionFlag1 =
IF(
ISBLANK(Reverse[PreviousRank]),
0,
IF(
Reverse[Rank] < Reverse[PreviousRank],
1,
0
)
)

Create a measure by calling the flag with area reversion.

Area_Reversions =
SUMX(
SUMMARIZE(
Reverse,
Reverse[Area],
Reverse[PBI],
"Reversions", MAX(Reverse[ReversionFlag1])
),
[Reversions]
)

it is working as expected, please check
Did I answer your question? Mark my post as a solution! This will help others on the forum!
2 REPLIES 2
Community Champion

Hi @PJVisscher89 - Create a calculated column in your sample table shared.

PreviousRank =
VAR CurrentDate = Reverse[Date]
RETURN
CALCULATE(
MAX(Reverse[Rank]),
FILTER(
Reverse,
Reverse[PBI] = EARLIER(Reverse[PBI]) &&
Reverse[Date] < CurrentDate
)
)

Create another calculated column with flag 0,1 in same table.

ReversionFlag1 =
IF(
ISBLANK(Reverse[PreviousRank]),
0,
IF(
Reverse[Rank] < Reverse[PreviousRank],
1,
0
)
)

Create a measure by calling the flag with area reversion.

Area_Reversions =
SUMX(
SUMMARIZE(
Reverse,
Reverse[Area],
Reverse[PBI],
"Reversions", MAX(Reverse[ReversionFlag1])
),
[Reversions]
)

it is working as expected, please check
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Frequent Visitor

That works like a charm, thanks for the quick help!