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

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft 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
      Icon for Microsoft Employee rankMicrosoft 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

      • nwitstine's avatar
        nwitstine
        Frequent Visitor
        7 years ago

        v-jiascu-msft The first solution doesn't work (for this sample yes) because my entire dataset is not this uniform - the dates vary a lot.

         

        I ended up going with the second solution - I wasn't able to use your formula when creating a custom column, but I was able to follow the advanced editor, from what you shared, to insert this into my table steps (Note: my previous step was "Added Refresh Date"):

         

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

        in

        #"Added Date Filter"

         //    (let currentProperty = [Property Code] in Table.SelectRows(#"Added Date Filter", each [Property Code] = currentProperty))[Area Date]

         

        I'm not exactly sure what the function of the last // clause is - it seems like I can remove it without affecting my table. 

         

        This works for now, but I would be interested in finding more ways to filter by date as I have more complicated calculations that I eventually want to apply based on when a row was entereing into a table. (i.e. multidate filters).