Forum Discussion
Filter Report by Max Date
Hello,
I am new to Power BI and would like help with this scenario I am not able to solve
The data that I have is as follows
| ID | Title | Team | Application | ChangedDate |
| 1 | Data1 | T1 | 8/30/2016 9:13:30 AM | |
| 1 | Data1 | T1 | A1 | 8/30/2016 10:33:40 AM |
| 1 | Data1 | T2 | A1 | 9/1/2016 7:53:30 PM |
| 2 | Data2 | 8/30/2016 9:23:30 AM | ||
| 2 | Data2 | T3 | A3 | 9/1/2016 2:53:30 PM |
What I would like to display in the report is only these rows that corresponds to the Max Changed Date
| ID | Title | Team | Application | ChangedDate |
| 1 | Data1 | T2 | A1 | 9/1/2016 7:53:30 PM |
| 2 | Data2 | T3 | A3 | 9/1/2016 2:53:30 PM |
Thanks for your help in advance,
Hi surajv3. One way you can do this is by going to the Data view and adding a new column that will indicate which row contains the max changed date by ID. Here's the column I used:
IsLatestChangedDateByID = TableName[ChangedDate] =
CALCULATE(
MAX(TableName[ChangedDate]),
FILTER(ALL(TableName), TableName[ID]=EARLIER(TableName[ID]))
)Using that will give you a True/False indicator that you can use in Filters, Slicers, etc.
Another way to get the same result is to use LASTNONBLANK instead of MAX:
IsLatestChangedDateByID = TableName[ChangedDate] = CALCULATE( LASTNONBLANK(TableName[ChangedDate], 1), FILTER(ALL(TableName), TableName[ID]=EARLIER(TableName[ID])) )
That's what I used at first, but then though the MAX solution is probably more intuitive. I only showed both so you know the options are there, and there are some cases where LASTNONBLANK will be more helpful than MAX.
4 Replies
Hi surajv3. One way you can do this is by going to the Data view and adding a new column that will indicate which row contains the max changed date by ID. Here's the column I used:
IsLatestChangedDateByID = TableName[ChangedDate] =
CALCULATE(
MAX(TableName[ChangedDate]),
FILTER(ALL(TableName), TableName[ID]=EARLIER(TableName[ID]))
)Using that will give you a True/False indicator that you can use in Filters, Slicers, etc.
Another way to get the same result is to use LASTNONBLANK instead of MAX:
IsLatestChangedDateByID = TableName[ChangedDate] = CALCULATE( LASTNONBLANK(TableName[ChangedDate], 1), FILTER(ALL(TableName), TableName[ID]=EARLIER(TableName[ID])) )
That's what I used at first, but then though the MAX solution is probably more intuitive. I only showed both so you know the options are there, and there are some cases where LASTNONBLANK will be more helpful than MAX.
Thank you so much for your help, Used the formula with MAX
Thanks again
Hi, Thanks for your tip. However, this is not working for me. I am wondering if it is comparing row to row so it's returning 'true' for all rows.
Thank you,
Inês Castelhano
