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 that the latest month data is choosen and that not everything is taken into account automatically,

 

Thanks in advance for your help,

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.

6 Replies

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