Forum Discussion
Matrix to Only Show Negative Values for Consecutive Months
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 |
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 __resultAdd 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.
3 Replies
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 __resultAdd 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.
