cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

LOOKUPVALE 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-old-Forecast-Value'.

 

Max date column is derived from the edited date column to identify the latest date and time an opportunity was edited. Now that i have the max date, i need to find out what is the associated latest 'old value'

Powerbi-SameVaue.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: LOOKUPVALE from the same table - Additional Info

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 name
 
I 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


Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

View solution in original post

Highlighted
Community Champion
Community Champion

Re: LOOKUPVALE from the same table - Additional Info

HI @KRISH80 ,

 

If this solution works for you, please mark it as a solution.

 

Thanks,

Pragati



Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

View solution in original post

6 REPLIES 6
Highlighted
Helper II
Helper II

Re: LOOKUPVALE from the same table - Additional Info

I tried using the DAX : 

Max Old Value =
VAR CurrentOppID = 'Krishna_Opp_Histrory_Forecast Category'[Max Date]
RETURN
MAXX(FILTER(ALL('Krishna_Opp_Histrory_Forecast Category'), 'Krishna_Opp_Histrory_Forecast Category'[Max Date] = CurrentOppID),'Krishna_Opp_Histrory_Forecast Category'[Old Value])
 
But it is not giving me the desired result. Pls see below :  The actual Max Old Value is'Best Case' but it is giving me 'Pipeline'.
All the below rows are from the Same Opportnity which got edited in different intervals.POwerbi-2.png

 

Highlighted
Community Champion
Community Champion

Re: LOOKUPVALE from the same table - Additional Info

Hi @KRISH80 ,

 

Try modifying your DAX as follows:

Max Old Value = MAXX(
                                      FILTER(ALL('Krishna_Opp_Histrory_Forecast Category'), '
                     Krishna_Opp_Histrory_Forecast Category'[Max Date] = EARLIER('Krishna_Opp_Histrory_Forecast Category'[Max Date])),
                    ' Krishna_Opp_Histrory_Forecast Category'[Old Value]
                                     )
 
If this doesn't work please attache some sample data to try on or .pbix file.
 
If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂
 
Thanks,
Pragati


Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂
Highlighted
Helper II
Helper II

Re: LOOKUPVALE from the same table - Additional Info

Thanks 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 ?

Highlighted
Community Champion
Community Champion

Re: LOOKUPVALE from the same table - Additional Info

HI @KRISH80 ,

 

You can upload a sample file here or you can send a private message.

 

Thanks,

Pragati



Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂
Highlighted
Community Champion
Community Champion

Re: LOOKUPVALE from the same table - Additional Info

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 name
 
I 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


Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

View solution in original post

Highlighted
Community Champion
Community Champion

Re: LOOKUPVALE from the same table - Additional Info

HI @KRISH80 ,

 

If this solution works for you, please mark it as a solution.

 

Thanks,

Pragati



Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors