Forum Discussion
Avoid Duplicate Values For X Month
Hello,
I would like to filter data based on the ID, to avoid seeing it every X months.
In theory this should work like this:
After filtering my data I will get a table like this:
| Date | ID | Filtered Value |
| 1/1/2020 | A | 1 |
| 1/1/2020 | B | 1 |
| 1/1/2020 | C | 1 |
| 2/1/2020 | A | 1 |
| 2/1/2020 | B | 1 |
| 3/1/2020 | C | 1 |
| 4/1/2020 | A | 1 |
| 4/1/2020 | B | 1 |
My objective is to avoid seeing the same ID for 2 months in a row (or 3 it can be variable). So i need to do a filter that will result on this:
| Date | ID | Filtered Value |
| 1/1/2020 | A | 1 |
| 1/1/2020 | B | 1 |
| 1/1/2020 | C | 1 |
| 3/1/2020 | C | 1 |
| 4/1/2020 | A | 1 |
| 4/1/2020 | B | 1 |
In which the lines of february got deleted because their IDs where on the table on the previous month.
Is it there any posibility to do this on PowerBI?
Thanks in advance
The logic from DataZoe should be working, but I'd like to modify the formula a little bit :
Filter Value 2 = VAR _lastmonthvalue = CALCULATE ( MAX('Table'[Filtered Value]), PREVIOUSMONTH ( 'Table'[Date]) ) RETURN IF ( ISBLANK ( _lastmonthvalue ), MAX('Table'[Filtered Value]), BLANK () )Then filter the measure is not blank:
Pbix attached,
3 Replies
The logic from DataZoe should be working, but I'd like to modify the formula a little bit :
Filter Value 2 = VAR _lastmonthvalue = CALCULATE ( MAX('Table'[Filtered Value]), PREVIOUSMONTH ( 'Table'[Date]) ) RETURN IF ( ISBLANK ( _lastmonthvalue ), MAX('Table'[Filtered Value]), BLANK () )Then filter the measure is not blank:
Pbix attached,
- Thanks! I tried this solution and it works. However, if you want to escalate this to avoid X months or to have several other filtered values how would you do it?
nicolasvargas you could try:
Filter Value 2 =
VAR _lastmonthvalue =
CALCULATE ( [Filtered Value], PREVIOUSMONTH ( DateTable[Date] ) )
RETURN
IF ( ISBLANK ( _lastmonthvalue ), [Filtered Value], BLANK () )This will see if there is a value for the previous month, and if there isn't it will show the value, otherwise it will show as blank, and blanks are not shown in the table.
