topic Re: Highlight combinations with consecutive negative or positive values from another measuer in DAX Commands and Tips

Highlight combinations with consecutive negative or positive values from another measuer

KMadsen — Thu, 06 Oct 2022 07:59:41 GMT

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!

Re: Highlight combinations with consecutive negative or positive values from another measuer

johnt75 — Thu, 06 Oct 2022 09:51:48 GMT

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