Forum Discussion

nwitstine's avatar
nwitstine
Frequent Visitor
7 years ago
Solved

Latest Date Filter

I have seen many solutions to this problem, but none of them seem to work for my dataset. I have a table listing property names, codes, area, and area date. I'd like to filter my table to only show one line item per property based on the most recent area date. Ideally, I would be able to do this with columns rather than measures so I could filter the table in query editer rather than in my workbook.

 

Here is a sample dataset: https://docs.google.com/spreadsheets/d/1GZyPaFkFhPf4v3dcgcFyPwgR2LeQwFlItf7jFVm5L5M/edit#gid=126207281

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Nicole,

     

    There could be three solutions. Please check out you Messages box and download the demo.

    1. If every property has all the same dates, you can filter them directly. Please refer to the snapshot below.

    Latest-Date-Filter1

    2. Add a column in the Query Editor and filter.

     

    if [Area Date] = List.Max(let currentProperty = [Property Code] 
    in Table.SelectRows(#"Changed Type", 
    each [Property Code] = currentProperty)[Area Date]) 
then 1 
else 0

    3. Use DAX formula.

     

     

    Table =
FILTER (
    ADDCOLUMNS (
        'Table3',
        "IfMax", CALCULATE (
            MAX ( Table3[Area Date] ),
            FILTER ( 'Table3', 'Table3'[Property Code] = EARLIER ( Table3[Property Code] ) )
        )
    ),
    [IfMax] = [Area Date]
)

     

     

    Best Regards,
    Dale

10 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi nwitstine,

     

    I don't have access to your linked file. 

    1. If you'd like to filter the table in the Query Editor, you need to create it in the Query Editor. 

    2. Why do you want to filter in the Query Editor?

    3. What should the values of the column be?

     

     

    Best Regards,
    Dale

    • nwitstine's avatar
      nwitstine
      Frequent Visitor
      7 years ago

      v-jiascu-msft I shared the doc with you.

       

      I updated the dataset to highlight the rows that I'd like to show after the filter is applied (i.e. white should be filtered out, yellow should remain). It should also be noted that while the "most recent" date in this sample dataset is 12/1/2016 for all properties, for my larger dataset that's not the case. The most recent date depends on when properties are bought, sold, and developed. 

       

      I understand the need to filter in the query editor, but I haven't found a solution for how it can be done. When I filter for most recent date, it finds the most recent line item and doesn't have a way to apply the "by property code" constraint that I need it to.

       

      I have read through several solutions on this and most involve creating a calculated column followed by a custom measure - I haven't gotten any of these to work with my data, and as I said, it would be preferable to filter out in the query editor.

       

      Thanks for your help,

      Nicole

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi Nicole,

         

        There could be three solutions. Please check out you Messages box and download the demo.

        1. If every property has all the same dates, you can filter them directly. Please refer to the snapshot below.

        Latest-Date-Filter1

        2. Add a column in the Query Editor and filter.

         

        if [Area Date] = List.Max(let currentProperty = [Property Code] 
    in Table.SelectRows(#"Changed Type", 
    each [Property Code] = currentProperty)[Area Date]) 
then 1 
else 0

        3. Use DAX formula.

         

         

        Table =
FILTER (
    ADDCOLUMNS (
        'Table3',
        "IfMax", CALCULATE (
            MAX ( Table3[Area Date] ),
            FILTER ( 'Table3', 'Table3'[Property Code] = EARLIER ( Table3[Property Code] ) )
        )
    ),
    [IfMax] = [Area Date]
)

         

         

        Best Regards,
        Dale

  • AdHuikeshoven's avatar
    AdHuikeshoven
    New Member
    2 years ago

    Here is my solution. In Power Query group on "Property Code" selecting Max of "Area Date" - name this "Max Date". Add and aggregate level "expansion" including all rows. Expand. Add a conditional column "Latest?" comparing "Area Date" with "Max Date". If equal enter "latest" otherwise "null". Filter on "latest" in the column "Latest?". Delete if necessary the columns "Max Date" and "Latest?"