Latest Date Filter

[nwitstine]

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=1262072...

[AdHuikeshoven]

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

[v-jiascu-msft]

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]

@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 · Accepted solution]

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.

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

[K_Baathe]

Thank you so much @v-jiascu-msft, the second solution works great!

You have saved me so much time with this! 🙂

I have a question about the third solution, which I would really like to use for another context:
I get the error:
"DAX comparison operations do not support comparing values of type Date with values of type Text."
which i believe is what is being done by the MAX function in my case. (I'm not having a property code, but a System ID, which is in text-format.) Even if you have a numerical value, it feels wrong to do MAX of the DATE and the Property Code/System ID. 
Or am I misunderstanding something? If you could fix the DAX, I would be very interested in what that would look like, since I'm trying to get the hang of EARLIER and such expressions.

[nwitstine]

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

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]

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]

Hi,

Write this calculated column formula

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

Hope this helps.

[nqzhhzmn]

My data is over 100,000 rows and that doesn't return results in finite time, I'm afraid.   I've bookmarked the solution in this thread, which works quickly.  Thanks.

Solved: Finding the most recent value - Microsoft Power BI Community

The solution is by CheenuSing on March 14, 2018 at 8:40 pm.