Forum Discussion
Calculate Number of Periods of Consecutive Increase/Decrease
I have data that looks like the first three columns here: I am wanting to calculate the fourth column, in italics. This number represents the number of consecutive months that the score has incr...
Anonymous
Can we do it in 2 steps
First this supporting calculated column
EachMonthChange =
VAR Mymonth = [Month]
RETURN
IF (
[Score]
> CALCULATE (
MIN ( Table1[Score] ),
FILTER ( Table1, Table1[Month] = Mymonth - 1 && [Site] = EARLIER ( [Site] ) )
),
1,
-1
)
Then the desired one
Sustaining Change = VAR mychange = [EachMonthChange] VAR result = MINX ( TOPN ( 1, FILTER ( Table1, [Month] < EARLIER ( [Month] ) && [EachMonthChange] <> mychange && [Site] = EARLIER ( [Site] ) ), [Month], DESC ), [Month] ) RETURN ( [Month] - IF ( ISBLANK ( result ), 1, result ) ) * [EachMonthChange]
Zubair_Muhammad
Community Champion
Community ChampionAnonymous
Can we do it in 2 steps
First this supporting calculated column
EachMonthChange =
VAR Mymonth = [Month]
RETURN
IF (
[Score]
> CALCULATE (
MIN ( Table1[Score] ),
FILTER ( Table1, Table1[Month] = Mymonth - 1 && [Site] = EARLIER ( [Site] ) )
),
1,
-1
)
Then the desired one
Sustaining Change =
VAR mychange = [EachMonthChange]
VAR result =
MINX (
TOPN (
1,
FILTER (
Table1,
[Month] < EARLIER ( [Month] )
&& [EachMonthChange] <> mychange
&& [Site] = EARLIER ( [Site] )
),
[Month], DESC
),
[Month]
)
RETURN
( [Month] - IF ( ISBLANK ( result ), 1, result ) ) * [EachMonthChange]