Latest Value

[jdugas]

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

Code	yearMonth	FIValue
BEVH	4/1/2014	93
BEVH	6/1/2015	94
BEVH	1/1/2016	95
BWH	1/1/2014	86
BWH	6/1/2015	98.2
BWH	4/1/2016	96.3
CATH	4/1/2013	80
CATH	6/1/2015	87
CH	6/1/2013	80
CH	5/1/2015	93
CMC	10/1/2013	96
CMC	9/1/2014	75
CMC	5/1/2015	75
CMC	12/1/2015	96
CMC	1/1/2016	96
CMC	2/1/2016	100
CMMC	8/1/2014	96.8
CMMC	5/1/2015	97
CMMC	12/1/2015	93
DHMC	1/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

[Maxime]

Use this formula:

Measure_LastValue := CALCULATE(SUM(YourTableName[YourFieldConcerned]); LASTDATE(YourTableName[FieldDate of YourTableName]))

[AlexaderMilland]

I can't see how this is working? 
If you specify the calendar table in the last date functions in returns nothing, since the calendar table has later dates than the orders/orderline table. 
If you specify an order/orderline table you have multiple "last dates" which isn't supported. 
Am i doing something wrong? 

[sewaktamang]

Hi,

You can just use IF ELSE and MAX DAX function to get latest date data within a second.

You can take refrence from following post also. 

How to get latest date and display latest date data

Let me know if you face any issue 

Thanks

[v-shex-msft]

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

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:

Regards,

Xiaoxin sheng

[Alex_Ooi]

---

@v-shex-msft wrote:

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

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:

Regards,

Xiaoxin sheng

---

I tried replicating this solution and I encountered an error where it says "A table of multiple values was supplied where a single value was expected."

I wonder what went wrong...

[kkalyanthaya]

Thank you @v-shex-msft for the answer. This works fine for me with imported data.

But for push data set data, the measure gets created but no data is shown. The grid appears blank. Is this solution not applicable for push data set ?

[willianmsantos]

Xiaoxin sheng shen!

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:

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

[willianmsantos]

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:

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

[tringuyenminh92 · Accepted solution]

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

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

Best regards
Tri Nguyen

[Oscar_Mtz_V]

Thanks!!! Great Answer!!!

[jdugas]

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

[jdugas]

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]

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? 

[Anonymous]

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

[Greg_Deckler]

You should just be able to use "MAX"