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