Forum Discussion
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 ...
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.
The issue now is that if you use the entire dummy data I shared (with Item Chair), the code stops working
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.