Matrix to Only Show Negative Values for Consecutive Months

[RichA08]

Hi All, 

I'm looking to create a matrix visual that only shows the items that have a negative or 0 for the last 3 consecutive months. Filtering seems to only apply to part of the data. So it might only filter for the first month, but not the months after that. How would I acheive for it to only show if each of the past 3 months have been negative or zero? 

Sample Dataset. 

ID	Month	Accuracy
1	Jan	-30
1	Feb	-1
1	March	7
2	Jan	-20
2	Feb	-30
2	March	-45
3	Jan	23
3	Feb	-20
3	March	14
4	Jan	-11
4	Feb	-20
4	March	-30
5	Jan	4
5	Feb	6
5	March	-30

[KNP · Accepted solution]

Hi @RichA08,

So, not sure exactly what your complete model looks like, so a couple of things first.

I added a Date column to the sample data and called it 'Date'.

Date = DATEVALUE("2025" & " " & 'Table'[Month ] & " 1")

I then created a measure...

ID Last 3 Months Non Positive = 
VAR __latestDate =
    CALCULATE(
        MAX('Table'[Date])
    ,	ALLEXCEPT('Table', 'Table'[ID])
    )
VAR __windowCount =
    CALCULATE(
        COUNTROWS('Table')
    ,	ALLEXCEPT('Table', 'Table'[ID])
    ,	DATESINPERIOD(
            'Table'[Date]
        ,	__latestDate
        ,	-2
        ,	MONTH
        )
    )
VAR __windowMax =
    CALCULATE(
        MAX('Table'[Accuracy])
    ,	ALLEXCEPT('Table', 'Table'[ID])
    ,	DATESINPERIOD(
            'Table'[Date]
        ,	__latestDate
        ,	-2
        ,	MONTH
        )
    )
VAR __result =
    IF(
        __windowCount > 0
            && __windowMax <= 0
    ,	1
    ,	0
    )
RETURN __result

Add this measure to the filter pane where the value is 1.

This should give you what you're looking for.

My code is often GitHub Copilot assisted, but unlike many others, tested to confirm results are correct.

[RichA08]

Thank you!! Worked beautifully