Skip to main content
cancel
Showing results for 
Search instead 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

Reply
PJVisscher89
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:

AreaPBIDateStatusRank
Area A11-1-2024Proposed1
Area A12-1-2024In Progress2
Area A13-1-2024Completed3
Area A21-1-2024Proposed1
Area A22-1-2024In Progress2
Area A23-1-2024Completed3
Area B31-1-2024Completed3
Area B32-1-2024In Progress2
Area B33-1-2024In Progress2
Area B41-1-2024Proposed1
Area B42-1-2024Completed3
Area B43-1-2024In Progress

2

 

Expected Result:

AreaReverse
Area A0
Area B2
1 ACCEPTED SOLUTION
rajendraongole1
Community Champion
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
    )
)
 
rajendraongole1_0-1718795267447.png
Create another calculated column with flag 0,1 in same table.
 
ReversionFlag1 =
IF(
    ISBLANK(Reverse[PreviousRank]),
    0,
    IF(
        Reverse[Rank] < Reverse[PreviousRank],
        1,
        0
    )
)
 
rajendraongole1_1-1718795316605.png

 

 

Create a measure by calling the flag with area reversion.

Area_Reversions =
SUMX(
    SUMMARIZE(
        Reverse,
        Reverse[Area],
        Reverse[PBI],
        "Reversions", MAX(Reverse[ReversionFlag1])
    ),
    [Reversions]
)
rajendraongole1_2-1718795386021.png
 
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!
Appreciate your Kudos!!

View solution in original post

2 REPLIES 2
rajendraongole1
Community Champion
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
    )
)
 
rajendraongole1_0-1718795267447.png
Create another calculated column with flag 0,1 in same table.
 
ReversionFlag1 =
IF(
    ISBLANK(Reverse[PreviousRank]),
    0,
    IF(
        Reverse[Rank] < Reverse[PreviousRank],
        1,
        0
    )
)
 
rajendraongole1_1-1718795316605.png

 

 

Create a measure by calling the flag with area reversion.

Area_Reversions =
SUMX(
    SUMMARIZE(
        Reverse,
        Reverse[Area],
        Reverse[PBI],
        "Reversions", MAX(Reverse[ReversionFlag1])
    ),
    [Reversions]
)
rajendraongole1_2-1718795386021.png
 
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!
Appreciate your Kudos!!

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors