Forum Discussion

KRISH80's avatar
KRISH80
Icon for Helper II rankHelper II
6 years ago
Solved

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-o...
  • Pragati11's avatar
    Pragati11
    6 years ago

    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
  • Pragati11's avatar
    Pragati11
    6 years ago

    HI KRISH80 ,

     

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

     

    Thanks,

    Pragati

KRISH80's avatar
KRISH80
Icon for Helper II rankHelper II
6 years ago

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.

 

Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
6 years ago

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
  • KRISH80's avatar
    KRISH80
    Icon for Helper II rankHelper II
    6 years ago

    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 ?

    • Pragati11's avatar
      Pragati11
      Icon for Super User rankSuper User
      6 years ago

      HI KRISH80 ,

       

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

       

      Thanks,

      Pragati

      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User
        6 years ago

        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