Forum Discussion

KMadsen's avatar
KMadsen
Regular Visitor
4 years ago
Solved

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 ...
  • johnt75's avatar
    johnt75
    4 years ago

    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

johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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