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 am having trouble with an already discussed topic in here but I have a slightly different version. My data structure is the following one:
id | value | Date entered |
1 | y | 10/12/2018 |
1 | x | 10/13/2018 |
2 | x | 10/12/2018 |
2 | z | 10/13/2018 |
2 | y | 10/16/2018 |
I want to add one new column which should have the latest Value per ID on each row. The solution should work when I apply the explicit filter on the Date entered.
I got the following working code already (This calculates me the max Date) :
max date =
var mind=MINX(ALLSELECTED('calendar'),'calendar'[Date])
var maxd=MAXX(ALLSELECTED('calendar'),'calendar'[Date])
return CALCULATE(MAX('Table 2'[dateentered]), FILTER(ALL('Table 2'), 'Table 2'[id]=MIN('Table 2'[id]) && 'Table 2'[dateentered]>=mind && 'Table 2'[dateentered]<=maxd))
Now I want to return the latest vale of the max Date. Therefore I got the following code but the Lookupvalue expression doesn't work because I have duplicates in the date entered column. Can someone please help me which expression I should use instead.
max value = LOOKUPVALUE('Table 2'[value],'Table 2'[dateentered],[max date])
The result should be the following one:
id | value | date entered | Latest Date | Latest Value |
1 | y | 10/12/2018 | 10/13/2018 | X |
1 | x | 10/13/2018 | 10/13/2018 | X |
2 | x | 10/12/2018 | 10/16/2018 | y |
2 | z | 10/13/2018 | 10/16/2018 | y |
2 | y | 10/16/2018 | 10/16/2018 | y |
Thank you very much
Solved! Go to Solution.
Hi @Anonymous,
Assuming you data Model looks like this
The following Measure provides the expected results
Date Latest Value =
var datesSelected = VALUES('Calendar'[Date])
var tid = VALUES('Table'[id])
var maxDate = CALCULATE(MAX('Table'[Date entered]),ALL('Table'), 'Table'[Date entered] in datesSelected, 'Table'[id] in tid)
var maxValue = CALCULATE(max('Table'[value]), FILTER(ALL('Table'), 'Table'[Date entered] = maxDate && 'Table'[id] in tid))
return maxValue
pbix file here, sample.pbix
I Hope this helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Hi @Anonymous,
Assuming you data Model looks like this
The following Measure provides the expected results
Date Latest Value =
var datesSelected = VALUES('Calendar'[Date])
var tid = VALUES('Table'[id])
var maxDate = CALCULATE(MAX('Table'[Date entered]),ALL('Table'), 'Table'[Date entered] in datesSelected, 'Table'[id] in tid)
var maxValue = CALCULATE(max('Table'[value]), FILTER(ALL('Table'), 'Table'[Date entered] = maxDate && 'Table'[id] in tid))
return maxValue
pbix file here, sample.pbix
I Hope this helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Please try this expression
Latest Value Measure =
VAR latestdate =
CALCULATE (
MAX ( Latest[date entered] ),
ALL ( Latest ),
VALUES ( Latest[id] )
)
RETURN
CALCULATE (
MIN ( Latest[value] ),
ALL ( Latest ),
VALUES ( Latest[id] ),
Latest[date entered] = latestdate
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@Anonymous , Try
lastnonblankvalue(Table[Date entered], max(Table[value]))
The option are use it with all except with ID
calculate(lastnonblankvalue(Table[Date entered], max(Table[value])),allexcept(Table[ID]))
@Anonymous - Use MAXX or MINX instead of LOOKUPVALUE. Basically this is the Lookup Min/Max pattern found here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |