Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
Lastest = SUMMARIZE(Data,Data[Code],"YearMonth",MAX(Data[YearMonth]))
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
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?
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
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
@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...
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:
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:
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
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:
Lastest = SUMMARIZE(Data,Data[Code],"YearMonth",MAX(Data[YearMonth]))
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
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?
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])))
You should just be able to use "MAX"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
79 | |
73 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |