Forum Discussion
Show only data from the latest date
Dear all,
I have a very basic question. I constantly struggle with showing only the data with the newest date. What is the easiest way to get a visual only to show data from the most recent date.
As I see it there are multiple ways – a measure, filter(although this does not work for me), row count in the source.
I have a simple example – data set:
Date |type |value|
20-06-2018 A 20
20-06-2018 B 40
19-06-2018 A 21
19-06-2018 B 39
And my table visual should then only show:
Date |type |value|
20-06-2018 A 20
20-06-2018 B 40
Many thanks
\Chr
Hi ChristianTD,
Seems date type values don't have a "TOP" filter type. I would suggest you try the measure below and add it to the "Visual Level filter". Don't need to add it to any visual. Then filter the filter as "1". Please give it a try.
Measure = VAR LatestDate = CALCULATE ( MAX ( 'Table1'[Date] ), ALL ( 'table1' ) ) RETURN IF ( MIN ( 'Table1'[Date] ) = LatestDate, 1, 0 )Best Regards,
Dale
28 Replies
One way is to add this calculated column to your table, which returns a 1 or 0 for latest/not latest which you can use as a filter
Is Latest Row Filter = VAR LatestDate = MAXX(FILTER('Table1','Table1'[Type] = EARLIER('Table1'[Type])),'Table1'[Date]) RETURN IF('Table1'[Date]=LatestDate,1,0)Thanks.
You would then apply a filter where this column is = 1, wouldn't you have to display this column in order for it to work?
would this be updated every time I do a refresh of data ?
- Correct. Just add it as a filter on your visual ser to 1
Hi Phil,
When i try to enter the function you suggested, i am getting "Token eof expected" error, cant figure out why? Could you help Please.
Just adding my $0.02 because this question is what I had, and I came to a different, simpler, solution.
on one of the visuals on the page, do a filter like this:
note: top N is only available on the visual. if you try to filter on the page, it won't show top n (just basic and advanced), but the filter can work to filter the entire page.
Phil_Seamark any suggestions on how to adapt this to get next closest date? i.e. this is great for max or min, but I need to subtract latest date from next closest date? There must be a way! Cheers.
I'm very late to the party, but this is what worked for me.
Add a new column (DAX measure) then
Column = IF(LOOKUPVALUE(Data_Bank[Date],Data_Bank[Date],MAX(Data_Bank[Date])) = Data_Bank[Date], 1, 0)Then just filter in the visuals for #1I know this is an old thread, but what if I want to pull the most recent date for a column while also preserving a filtered column. Example: I have raw materials that go to different warehouses and the unit price can be different between warehouses. The file I have includes unit price updates. I used the formula from this thread and it works, but it only pulls the price for the most recent date regardless of warehouse.
Warehouse Raw Material Unit Price Price Update W1 Raw A $ 4.00 11/1/2022 W1 Raw A $ 3.00 10/1/2022 W1 Raw A $ 2.00 9/1/2022 W1 Raw A $ 1.00 8/1/2022 W2 Raw A $ 4.25 11/1/2022 W2 Raw A $ 3.25 10/1/2022 W2 Raw A $ 2.25 9/1/2022 W2 Raw A $ 1.25 8/1/2022 W1 Raw B $ 3.00 10/5/2022 W1 Raw B $ 2.75 9/5/2022 W1 Raw B $ 2.50 8/5/2022 W1 Raw B $ 2.25 7/5/2022 W2 Raw B $ 2.00 11/1/2022 W2 Raw B $ 1.75 10/1/2022 W2 Raw B $ 1.50 9/1/2022 W2 Raw B $ 1.25 8/1/2022 W1 Raw C $ 2.00 11/1/2022 W1 Raw C $ 1.75 10/1/2022 W2 Raw C $ 2.50 9/1/2022 W2 Raw C $ 2.25 8/1/2022 Hi,
Show the expected result very clearly.
In the original example, this solution would provide the most recent entries in the table (although I get 0 for all entries). However, I'm wondering how I could have it get just the most recent entries per group, as my dates are not necessarily like above.
For example
SN Date
123 2018-10-23
123 2019-03-14
123 2022-12-03
456 2019-12-25
456 2020-01-01
456 2022-05-15
789 2022-01-31
789 2023-09-28
Should return:
SN Date
123 2022-12-03
456 2022-05-15
789 2023-09-28
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"SN", Int64.Type}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"SN"}, {{"Count", each List.Max([Date]), type nullable date}}) in #"Grouped Rows"Hope this helps.
