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

  • 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

1 Reply

  • 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