Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

    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]