Count Consecutive Increasing Amounts

[PowerBITestingG]

I have the following table. I need a calculated column that returns the count of consecutive LotNumber that has an increasing Amount. I have browsed similar solution but I cant get one to work on my table without crashing the pbix file

Item	LotNumber	Amount	IncreasingConsecutiveCount
Table	1	10	1
Table	3	20	2
Table	5	30	3
Table	9	20	1
Table	11	10	1
Table	13.6	20	2
Table	16.2	90	3
Table	18.8	10	1
Table	21.4	11	2
Table	24	12	3
Table	25	13	4
Table	26	14	5

[ThxAlot]

[PowerBITestingG]

Thank you for that. Would you know why when you add another column everything turns to 1?

[Anonymous]

Hi,

Thanks for the solution @ThxAlot provided, it is excellent, and i want to offer some more information for user to refer to.

hello @PowerBITestingG , based on your descriotion, you can create the following calcualted columns.

Rank = RANKX('Table',[LotNumber],,ASC)

Flag =
VAR a =
    LOOKUPVALUE ( 'Table'[Amount], [Rank], [Rank] - 1 )
RETURN
    IF ( [Amount] > a, 1, 0 )

ConsecutiveCount =
VAR a =
    MAXX (
        FILTER ( 'Table', [Flag] = 0 && [Rank] <= EARLIER ( 'Table'[Rank] ) ),
        [Rank]
    )
RETURN
    SWITCH ( TRUE (), a = BLANK (), [Rank], [Rank] = a, 1, [Rank] - a + 1 )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerBITestingG]

The issue now is that if you use the entire dummy data I shared (with Item Chair), the code stops working 

[Anonymous · Accepted solution]

Hi @PowerBITestingG 

You can try the following modified calculated columns

Rank =
RANKX (
    FILTER ( 'Table', [Item] = EARLIER ( 'Table'[Item] ) ),
    [LotNumber],
    ,
    ASC
)

Flag =
VAR a =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        ALLEXCEPT ( 'Table', 'Table'[Item] ),
        'Table'[Rank]
            = EARLIER ( 'Table'[Rank] ) - 1
    )
RETURN
    IF ( [Amount] > a, 1, 0 )

ConsecutiveCount =
VAR a =
    MAXX (
        FILTER (
            'Table',
            [Flag] = 0
                && [Item] = EARLIER ( 'Table'[Item] )
                && [Rank] <= EARLIER ( 'Table'[Rank] )
        ),
        [Rank]
    )
RETURN
    SWITCH ( TRUE (), a = BLANK (), [Rank], [Rank] = a, 1, [Rank] - a + 1 )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.