Filter data with a measure containing a date

[Anonymous]

Hello everyone, 

I am trying to figure out how to filter data with a measure containing the latest month where data have been uploaded to the pwbi dataset. I want to be able to make by default that the latest month data is choosen and that not everything is taken into account automatically,

Thanks in advance for your help,

[Greg_Deckler]

@Anonymous 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.

[Anonymous]

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 

[Greg_Deckler · Accepted solution]

@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.

[Anonymous]

Thanks @Greg_Deckler, that was exactly what I was searching for !

Have a good day !

[Anonymous]

@Greg_Deckler  just a quick question I forgot to ask, why make a temporary table filtered containing only the id's ? I may think it's for efficiency but I want to be sure,

Thanks in advance,

[Greg_Deckler]

@Anonymous It doesn't actually have just id column in the temp table, it is only considering the id column when filtering the table.