Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter data with a measure containing a date

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 th...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

Thanks for your answer,

 

My data looks like this: 

 

published (date)idis_somethingsales
saturday 1st february 2020123true1,000
sunday 1st march 2020123true1,230
wednesday 1st april 2020123false1899

 

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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

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

     

    Have a good day !

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        5 years ago

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