Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jdugas
Helper I
Helper I

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
tringuyenminh92
Memorable Member
Memorable Member

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

16 REPLIES 16
Maxime
Advocate I
Advocate I

Use this formula:

 

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

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
Frequent Visitor

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

willianmsantos
Frequent Visitor

 
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.


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

 

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


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

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 ?

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:

 

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

 

 

 

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

 

 

 

tringuyenminh92
Memorable Member
Memorable Member

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

Thanks!!! Great Answer!!!

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

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

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
Not applicable

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
Super User
Super User

You should just be able to use "MAX"


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors