Show only data from the latest date

[ChristianTD]

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

[zenmonkey]

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

[Ashish_Mathur]

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.

[jondufault]

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. 

[mcash]

I 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

[Ashish_Mathur]

Hi,

Show the expected result very clearly.

[Anonymous]

HI ,

I tried this and the measure always gives me 0

[rolopez21]

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

[FayeB1901]

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

[Phil_Seamark]

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)

[raphazzz]

I came accross your proposed solution but it is returning 1 for either the lastest or oldest submission...

Update = 
VAR Last =
    MAXX(
        FILTER ( 'Sales', 'Sales'[submission_date] = EARLIER ( 'Sales'[submission_date] ) ),
        'Sales'[submission_date]
    )
RETURN
    IF ( 'Sales'[submission_date] = Last, 1, 0 )

These are results for the same branch, this table has 9 variables but the only the brach name is the same amd projection month have the same data. 

[Anonymous]

This worked for me thanks:)

[SachinSawant]

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.

[ChristianTD]

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 ?

[Phil_Seamark]

Correct. Just add it as a filter on your visual ser to 1

[ChristianTD]

I would like not to show the new column.

I also tried with a measure Latest = LOOKUPVALUE(Table[Date];Table[Date];MAX(Table[Date])) and then adding my date to the filter and add top 1 by Latest.

Alternatively wouldn't just a simple filter work.. date top 1 by date?

[v-jiascu-msft · Accepted solution]

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

[Burk]

Hi everyone,

I have got some GSM datas with control date and hour. 

In some situations, their tariffs plans are changed and when we report this table we see two plans for a number.

So I want to filter last day and last hour report in my table. 

Here is my fake sample data. In this source of table we control available balance. So in Power BI we report Plan and Balance stiuations. So when i sort september value i have to see in this table just 4.09.2022 and 09:32 line. 

GSM Number	Tariff Plan	Available Balance	Date	Time
53xxx56	20 GB	10%	2.09.2022	08:43
53xxx56	50 GB	30%	3.09.2022	10:40
53xxx56	100 GB	90%	3.09.2022	11:12
53xxx56	30 GB	10%	4.09.2022	08:30
53xxx56	60 GB	70%	4.09.2022	09:32

Thank you so much.

[allwynbazil]

Hi @v-jiascu-msft ,

I am very new to PowerBI and have tried the accepted solution above. However, I don't understand how to set the visual filter as 1. I am trying to pull the progress for the latest date. Please help me.

Regards,

Alwin

[Anonymous]

hi @v-jiascu-msft 

i had a situation, how one can get last row data of every year to compare / calculate difference?

Sample Data:

Country Name  Date         Price
------------  -----------  ------
Argentina     3/1/2015     3.40
Argentina     6/28/2015    3.46
Argentina     3/1/2016     3.50
Argentina     6/28/2016    3.51
Argentina     4/1/2017     3.60
Argentina     8/29/2017    3.66
Argentina     5/1/2018     3.70
Argentina     8/30/2018    3.73

Brazil        3/1/2015     2.23
Brazil        6/28/2015    2.30
Brazil        3/1/2016     2.38
Brazil        6/28/2016    2.44
Brazil        2/8/2017     2.50
Brazil        7/31/2017    2.59
Brazil        3/13/2018    2.70
Brazil        7/22/2018    2.77

Output on Visual:

Country Name   Date        Price   Difference
------------   ---------   -----   ----------
Argentina      6/28/2015    3.46
Argentina      6/28/2016    3.51         0.05
Argentina      7/31/2017    3.66         0.15
Argentina      7/22/2018    3.73         0.07

Brazil         6/28/2015    2.30
Brazil         6/28/2016    2.44         0.14
Brazil         7/31/2017    2.61         0.17
Brazil         7/22/2018    2.77         0.16

please help, how i can achieve this as most of the solutions are accessing Last Most when searched here and on google.

regards

[ppatel91]

I used this same measure and I applied this on visual level filter and filtered as "1", it seems to be giving me min and max date but my other visuals are not reflecting according to date. I am very new to DAX and I dont know what I need to provide after filtered to "1" ? the filter pan has "And" "Or" selection and I don't know what condition I need to provide. Is that something affecting to my visuals and they are not updating? Please help me how can I resolve this.