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

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
      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User
        6 years ago

        HI KRISH80 ,

         

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

         

        Thanks,

        Pragati