cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChristianTD
Frequent Visitor

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

1 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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

24 REPLIES 24
mcash
Frequent Visitor

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.

 

WarehouseRaw MaterialUnit PricePrice Update
W1Raw A $          4.0011/1/2022
W1Raw A $          3.0010/1/2022
W1Raw A $          2.009/1/2022
W1Raw A $          1.008/1/2022
W2Raw A $          4.2511/1/2022
W2Raw A $          3.2510/1/2022
W2Raw A $          2.259/1/2022
W2Raw A $          1.258/1/2022
W1Raw B $          3.0010/5/2022
W1Raw B $          2.759/5/2022
W1Raw B $          2.508/5/2022
W1Raw B $          2.257/5/2022
W2Raw B $          2.0011/1/2022
W2Raw B $          1.7510/1/2022
W2Raw B $          1.509/1/2022
W2Raw B $          1.258/1/2022
W1Raw C $          2.0011/1/2022
W1Raw C $          1.7510/1/2022
W2Raw C $          2.509/1/2022
W2Raw C $          2.258/1/2022

Hi,

Show the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
reshma_p
New Member

HI ,

I tried this and the measure always gives me 0

rolopez21
New Member

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

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

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)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

This worked for me thanks:)

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.

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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?

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 NumberTariff Plan Available BalanceDateTime
 53xxx5620 GB 10%2.09.202208:43
 53xxx5650 GB30%3.09.202210:40
 53xxx56100 GB90%3.09.202211:12
 53xxx5630 GB10%4.09.202208:30
 53xxx5660 GB70%4.09.202209:32

 

Thank you so much.

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

 

allwynbazil_0-1662132859367.png

 

Anonymous
Not applicable

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

 

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.

In the accepted solution why does this work with either MIN or MAX in the IF logic . It is making my brain swim

 

Anonymous
Not applicable

Thank you so much. 
But, can we filter all visuals for the latest?

Anonymous
Not applicable

@v-jiascu-msft  Hi is there a way to do this in edit query? so that when my data is refreshed, it is much faster..

Anonymous
Not applicable

Hi @Anonymous 

 

A sample PowerQuery/M (from Query Editor) to get only latest records as below. To reduce confusion between various reserved words, I have added "Col" to all column names:

 

let
Source = Table1,
#"Grouped Rows" = Table.Group(Source, {"DateCol"}, {{"MaxDateCol", each List.Max([DateCol]), type date}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"DateCol"}, Table1, {"DateCol"}, "Table1", JoinKind.Inner),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1",
{ "TypeCol", "ValueCol"},
{ "Table1.TypeCol", "Table1.ValueCol" }
)
in
#"Expanded Table1"

 

 

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.