cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jorio Frequent Visitor
Frequent Visitor

Calculate Number of Periods of Consecutive Increase/Decrease

I have data that looks like the first three columns here:change.png

I am wanting to calculate the fourth column, in italics. This number represents the number of consecutive months that the score has increased or decreased leading up to that point: for example, in September, Site A's score had decreased for three months in a row (80% -> 20% -> 10% -> 0%), so its sustaining change was -3. In February, meanwhile, its score had increased for just one month (60% -> 70%) so its sustaining change was +1.

 

I have spent quite some time playing around with DAX functions and table variables trying to find a solution to do this but am finding it a bit too complicated - any suggestions?

 

(If relevant, I do have a Date table linked to my table via a "First Day of Month" column)

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Calculate Number of Periods of Consecutive Increase/Decrease

@jorio 

 

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]



Try my new Power BI game Cross the River

View solution in original post

3 REPLIES 3
Highlighted
Super User
Super User

Re: Calculate Number of Periods of Consecutive Increase/Decrease

@jorio 

 

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]



Try my new Power BI game Cross the River

View solution in original post

Super User
Super User

Re: Calculate Number of Periods of Consecutive Increase/Decrease

@jorio 

 

Attaching the pbix file as well with your sample data

canumber.png

Try my new Power BI game Cross the River
butlerjay87 Frequent Visitor
Frequent Visitor

Re: Calculate Number of Periods of Consecutive Increase/Decrease

Hi,

I am really interested in this for an SPC chart when looking to plot runs of increases/decreases. However, I currently have more than just "Site" to filter to. I would also like to be able to slice on the following fields: Site, Specialty, POD.

Because my visuals and the SPC chart are driven by those slicers, is it possible to come up with a calculated measure for this?

For example, chart for Site A, all Specs, all POD's may show sequences of increases/decreases but slicing the the Spec to 130 and POD to 1st Attendance would need to re-calculate the sequences.

Also, I am not working with month numbers but MonthDateStart (01/01/2000, 01/02/2000 etc)

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)