cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jdugas Regular Visitor
Regular Visitor

Latest Value

Hi

 

I'm trying to show only the latest value based on latest date and can't quite figure out what to do. I've tried filtering using LASTDATE but that doesn't appear to be working.

 

Here's a sample set

 

CodeyearMonthFIValue
BEVH4/1/201493
BEVH6/1/201594
BEVH1/1/201695
BWH1/1/201486
BWH6/1/201598.2
BWH4/1/201696.3
CATH4/1/201380
CATH6/1/201587
CH6/1/201380
CH5/1/201593
CMC10/1/201396
CMC9/1/201475
CMC5/1/201575
CMC12/1/201596
CMC1/1/201696
CMC2/1/2016100
CMMC8/1/201496.8
CMMC5/1/201597
CMMC12/1/201593
DHMC1/1/2014

96

  

 

 

So, for example, for Code CMC I should only see FIValue 100. I should only see 1 FIValue for each code based on date.

 

I attempted to follow this article but it's not working...

 

https://blogs.msdn.microsoft.com/lukaszp/2015/08/08/finding-the-latest-date-in-power-bi-desktop/

 

Thanks

Jeremy

1 ACCEPTED SOLUTION

Accepted Solutions
tringuyenminh92 New Contributor
New Contributor

Re: Latest Value

Hi @jdugas,

 

There are some solutions creating measure that will work with chart but not working in table, so my solution is focusing on group by data by Code and Max date column:

  • Create group by table with 2 column Code and Max YearMonth of that code
Lastest = SUMMARIZE(Data,Data[Code],"YearMonth",MAX(Data[YearMonth]))
  • Join original table with above table to get the value
Lastest Values = SUMMARIZE( FILTER(CROSSJOIN(Data,Lastest),Data[Code]=Lastest[Code] && Data[YearMonth]=Lastest[YearMonth]),Data[Code],Data[YearMonth],Data[FIValue])

Screenshot 2016-12-13 01.05.18.png

 

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

View solution in original post

14 REPLIES 14
Super User
Super User

Re: Latest Value

You should just be able to use "MAX"


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

cabelo Frequent Visitor
Frequent Visitor

Re: Latest Value

Use the "max" to filter the table, and then get sum or min or lastnoempty or... any measure

try this:

 

 

Last FI Value = CALCULATE( SUM([FIValue]); FILTER(Table;[yearMonth]=MAX([yearMonth])))
tringuyenminh92 New Contributor
New Contributor

Re: Latest Value

Hi @jdugas,

 

There are some solutions creating measure that will work with chart but not working in table, so my solution is focusing on group by data by Code and Max date column:

  • Create group by table with 2 column Code and Max YearMonth of that code
Lastest = SUMMARIZE(Data,Data[Code],"YearMonth",MAX(Data[YearMonth]))
  • Join original table with above table to get the value
Lastest Values = SUMMARIZE( FILTER(CROSSJOIN(Data,Lastest),Data[Code]=Lastest[Code] && Data[YearMonth]=Lastest[YearMonth]),Data[Code],Data[YearMonth],Data[FIValue])

Screenshot 2016-12-13 01.05.18.png

 

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

View solution in original post

Community Support Team
Community Support Team

Re: Latest Value

Hi @jdugas,

 

According to your description, you want get lastdate's value of each type, right?

 

If this is a case, you can refer to below formula to get the result table:

 

Table formula:

Result Table = ADDCOLUMNS(SUMMARIZE(Sheet5,Sheet5[Code],"MaxDate",MAX(sheet5[yearMonth])),"Value",LOOKUPVALUE(Sheet5[FIValue],Sheet5[Code],[Code],Sheet5[yearMonth],[MaxDate]))

 

Capture.PNG

 

In addition, you can also use measure to get the result value:

 

LastValue =
var curr= LASTNONBLANK(Sheet5[Code],[Code])
return
LOOKUPVALUE(Sheet5[FIValue],Sheet5[yearMonth],MAXX(FILTER(ALL(Sheet5),Sheet5[Code]=curr),[yearMonth]),Sheet5[Code],curr)

 

Visual:

Capture2.PNG

 

Regards,

Xiaoxin sheng

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



For learning resources/Release notes, please visit: | |
jdugas Regular Visitor
Regular Visitor

Re: Latest Value

Looks like I got it to work....or you did! Thanks

jdugas Regular Visitor
Regular Visitor

Re: Latest Value

How would I filter these tables? I have a table with many values and each record has an ID (DsfID). I can't seem to get this to work until I filter the DsfID at the query level. When I do that, my report breaks other visuals using the same table but with a different DsfID.

 

DsfID is a field in a table that tracks multiple datasets which don't necessarily relate to each other. It's a table that enables us to capture data on our members such as satisfaction scores, target patient census numbers, contributions to profitability, and more. My above challenge is something I will need to do for multiple DsfIDs so I'm looking for a way to filter at the SUMMARIZE level and not at the report level as a whole.

 

Jeremy

tringuyenminh92 New Contributor
New Contributor

Re: Latest Value

Hi @jdugas,

 

 

I'm not sure that i understand your point or not. Does this description relate the main topic or this is another question? 

Could you show me the some fields in data table and your expectation as picture? 

willianmsantos Frequent Visitor
Frequent Visitor

Re: Latest Value

 
willianmsantos Frequent Visitor
Frequent Visitor

Re: Latest Value

Hello Xiaoxin sheng and community!

 

 

Fisrt of all thanks for your posts and contribution, I wanted to reach the same results with a more extensive table, I followed the same Parameters from your solution:

 

*** Your code

LastValue =
var curr= LASTNONBLANK(Sheet5[Code];[Code])
return
LOOKUPVALUE(Sheet5[FIValue];Sheet5[yearMonth];MAXX(FILTER(ALL(Sheet5);Sheet5[Code]=curr);[yearMonth]);Sheet5[Code];curr)

 

*** My code adapted to my instance:

 

SG_LastValue =
var curr= LASTNONBLANK(SG_View_Jan_Abr[INSTANCE];[INSTANCE])
return
LOOKUPVALUE(SG_View_Jan_Abr[Allocated Capacity (GB)];SG_View_Jan_Abr[DATE].[Date];MAXX(FILTER(ALL(SG_View_Jan_Abr);SG_View_Jan_Abr[INSTANCE]=curr);[DATE]);SG_View_Jan_Abr[INSTANCE];curr)

 

 

I was expecting to get the last value based on the most recent date of each instance:

 

But when I tested that, it brings me blank values:

 

SG_View_pic.JPG

 

 

Can you help me and tell me if more parameters are needed or I missing anything? Basically I want to get from the table bellow a relation of INSTANCES and their Last Allocated capacity based on the most recent date. Bellow is a link of the table

 

https://drive.google.com/open?id=0Bx1SM-BuvfI4d1hGT0Rja3JncWM

 

 

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 63 members 1,038 guests
Please welcome our newest community members: