Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
KMadsen
Regular Visitor

Highlight combinations with consecutive negative or positive values from another measuer

Hi All,

I want to create a table that only contains planning combinations (region+demand group) which have had three consecutive periods with either negative or positive value. In the example below the table should only show the combination of Region A and Demand Group 1 and NOT Region B and Demand Group B. Can I set that up with filters on the current data or do I need a new measure to use as filter? How would that measure look like?

 

RegionDemand groupMonth_YearError
A1Sep, 2022-2
A1Aug, 2022-5
A1Jul, 2022-7
B1Sep, 2022-5
B1Aug, 2022-8
B1Jul, 2022+9

 

Thanks!

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You can add a calculated column like

Num consecutive = 
VAR CurrentError = 'Table'[Error]
VAR CurrentDate = 'Table'[Month_Year]
VAR StartDate = EOMONTH(CurrentDate, -3) + 1
VAR SummaryTable = CALCULATETABLE( 'Table',
    ALLEXCEPT('Table', 'Table'[Region], 'Table'[Demand group] ), 
    'Table'[Month_Year] >= StartDate && 'Table'[Month_Year] <= CurrentDate,
    SWITCH( SIGN(CurrentError),
        -1, 'Table'[Error] < 0,
        1, 'Table'[Error] > 0,
        FALSE()
    )
)
return COUNTROWS( SummaryTable )

The Year Month column needs to be an actual date.

You can then filter the table for any rows where Num consecutive is >= 3

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

You can add a calculated column like

Num consecutive = 
VAR CurrentError = 'Table'[Error]
VAR CurrentDate = 'Table'[Month_Year]
VAR StartDate = EOMONTH(CurrentDate, -3) + 1
VAR SummaryTable = CALCULATETABLE( 'Table',
    ALLEXCEPT('Table', 'Table'[Region], 'Table'[Demand group] ), 
    'Table'[Month_Year] >= StartDate && 'Table'[Month_Year] <= CurrentDate,
    SWITCH( SIGN(CurrentError),
        -1, 'Table'[Error] < 0,
        1, 'Table'[Error] > 0,
        FALSE()
    )
)
return COUNTROWS( SummaryTable )

The Year Month column needs to be an actual date.

You can then filter the table for any rows where Num consecutive is >= 3

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors