Forum Discussion
KRISH80
Helper II
Helper IILOOKUPVALE from the same table
Hi Team, I need hep to get the related value from the same table. I have a tabe below named Forecast Histroy and i need to find out the associated 'Old Value' for the 'Max Date' in the column 'Max-o...
Hi KRISH80 ,
This was tricky thing to resolve, but I have got a way to do it.
You MAX DATE date is fine.
For Maximum OLD Value, use the following DAX expression:
Max old value = CALCULATE(MAX('Sheet1 (2)'[Old Value]), FILTER('Sheet1 (2)', 'Sheet1 (2)'[Max Date] = EARLIER('Sheet1 (2)'[Max Date]) && 'Sheet1 (2)'[Opportunity ID] = EARLIER('Sheet1 (2)'[Opportunity ID]) && 'Sheet1 (2)'[Edit Date] = 'Sheet1 (2)'[Max Date test]))NOTE: Replace 'Sheet1 (2)' --> Your table nameI have tested this in Power BI using the sample data that you shared and it works perfectly fine!If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂Thanks,Pragati
KRISH80Helper II
Helper IIThanks a lot Pragati.. butit is not helping with opportunities which have multiple entries. Can you help me with your email id pls ? such that i can share some sample file with you ?
Pragati11
Super User
Super User- Pragati11
Hi KRISH80 ,
This was tricky thing to resolve, but I have got a way to do it.
You MAX DATE date is fine.
For Maximum OLD Value, use the following DAX expression:
Max old value = CALCULATE(MAX('Sheet1 (2)'[Old Value]), FILTER('Sheet1 (2)', 'Sheet1 (2)'[Max Date] = EARLIER('Sheet1 (2)'[Max Date]) && 'Sheet1 (2)'[Opportunity ID] = EARLIER('Sheet1 (2)'[Opportunity ID]) && 'Sheet1 (2)'[Edit Date] = 'Sheet1 (2)'[Max Date test]))NOTE: Replace 'Sheet1 (2)' --> Your table nameI have tested this in Power BI using the sample data that you shared and it works perfectly fine!If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂Thanks,Pragati