Forum Discussion
Filter data with a measure containing a date
Anonymous So it seems like you want something that I call a Complex Selector. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
In your case it would be something like:
Selector Measure = VAR __CurrentDate = MAX('Table'[published (date)]) VAR __Table = FILTER('Table',ALLEXCEPT('Table','Table'[id])) VAR __MaxDate = MAXX(__Table,[published (date)]) RETURN IF(__CurrentDate = __MaxDate,1,0)You can then use this measure in the filter pane and filter for 1. __CurrentDate gets the current row's date and compares with the maximum date for that id. If they match, returns 1 otherwise 0. You could also do this as a column.
Community ChampionAnonymous This looks like Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Or maybe you just need LASTNONBLANK or LASTNONBLANKVALUE, hard to tell without sample data.
Thanks for your answer,
My data looks like this:
published (date) id is_something sales saturday 1st february 2020 123 true 1,000 sunday 1st march 2020 123 true 1,230 wednesday 1st april 2020 123 false 1899 And I have a measure containing the maximum date of the current data that have been uploaded, here it would be wednesday 1st April 2020.
As I have hundreds of columns, I do not want make a measure everytime I want to use data the latest data so I was thinking of using filters to show the latest data, this way the report can be actualised automatically when new data is uploaded (normally it should be each month but it can be different times to times) and there are several rows that are each unique within a certain month, an id won't appear two times in the same month
But when I tried to use filters with published date, I saw that the only possibilities are using a fixed date, or a fixed interval (30 days ago for example) and that I couldn't do 'my measure' == 'published' nor make my measure be the default date for the data that would be taken into account on this report.
I don't know if it's clearer now for you, if you have any question please ask.
Thanks for your time,
- Greg_Deckler
Anonymous So it seems like you want something that I call a Complex Selector. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
In your case it would be something like:
Selector Measure = VAR __CurrentDate = MAX('Table'[published (date)]) VAR __Table = FILTER('Table',ALLEXCEPT('Table','Table'[id])) VAR __MaxDate = MAXX(__Table,[published (date)]) RETURN IF(__CurrentDate = __MaxDate,1,0)You can then use this measure in the filter pane and filter for 1. __CurrentDate gets the current row's date and compares with the maximum date for that id. If they match, returns 1 otherwise 0. You could also do this as a column.