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
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
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
Highlighted
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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)