Highlight combinations with consecutive negative or positive values from another measuer

[KMadsen]

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?

Region	Demand group	Month_Year	Error
A	1	Sep, 2022	-2
A	1	Aug, 2022	-5
A	1	Jul, 2022	-7
B	1	Sep, 2022	-5
B	1	Aug, 2022	-8
B	1	Jul, 2022	+9

Thanks!

[johnt75 · Accepted solution]

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