Forum Discussion

PowerBITestingG's avatar
PowerBITestingG
Resolver I
2 years ago
Solved

Count Consecutive Increasing Amounts

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

PowerBITestingG's avatar
PowerBITestingG
Resolver I
2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      PowerBITestingG
      Resolver I
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.