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

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?"