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

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

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

    Hi nwitstine,

     

    Sorry for the confusion. That's just a note. You can delete it. If you have more requirements, please feel free to post here.

     

     

    Best Regards,
    Dale

    • nqzhhzmn's avatar
      nqzhhzmn
      Advocate II
      5 years ago

      There are about 20 solutions to this elementary problem on this board, and I can't get any of them to work.  Let's say I have a table with one column in it called Date.  I want another column called IsLatest that is a one if the date is the latest date, otherwise zero.  How can I do this with a minimum of fuss?  I would have thought that MSFT would have built this common function in.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        Write this calculated column formula

        =if(Data[Date]=max(Data[Date]),1,0)

        Hope this helps.