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 CodeyearMonthFIValueBEVH4/1/201493BEVH6/1/201594BEVH1/1/201695BWH1/1/201486BWH6/1/201598.2BWH4/1/201696.3CATH4/1/201380CATH6/1/201587CH6/1/201380CH5/1/201593CMC10/1/201396CMC9/1/201475CMC5/1/201575CMC12/1/201596CMC1/1/201696CMC2/1/2016100CMMC8/1/201496.8CMMC5/1/201597CMMC12/1/201593DHMC1/1/201496 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/ ThanksJeremy

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 codeLastest = SUMMARIZE(Data,Data[Code],"YearMonth",MAX(Data[YearMonth]))Join original table with above table to get the valueLastest 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 regardsTri Nguyen

Latest Value | Microsoft Fabric Community

16 Replies

tringuyenminh92
Memorable Member
9 years ago
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
  Kudo Commander
  8 years ago
  Thanks!!! Great Answer!!!
- jdugas
  Advocate I
  9 years ago
  Looks like I got it to work....or you did! Thanks
  - jdugas
    Advocate I
    9 years ago
    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
Maxime
Advocate I
8 years ago
Use this formula:

Measure_LastValue := CALCULATE(SUM(YourTableName[YourFieldConcerned]); LASTDATE(YourTableName[FieldDate of YourTableName]))
- AlexaderMilland
  Helper III
  3 years ago
  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?
Anonymous
Not applicable
9 years ago
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])))
Anonymous
Not applicable
9 years ago
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
- willianmsantos
  Frequent Visitor
  9 years ago
  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
- willianmsantos
  Frequent Visitor
  9 years ago
  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
- kkalyanthaya
  Microsoft Employee
  8 years ago
  Thank you Anonymous 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 ?
Greg_Deckler
Community Champion
9 years ago
You should just be able to use "MAX"
willianmsantos
Frequent Visitor
9 years ago
sewaktamang
Frequent Visitor
8 years ago
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

Forum Discussion

Latest Value

16 Replies

Related content

Latest Monthly Value measure that resets automatically

Latest value

Show Latest Value & Last N Values

Latest Month value

Get latest date values

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