Forum Discussion

KRISH80's avatar
KRISH80
Helper 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-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'

 

  • 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

6 Replies

  • KRISH80's avatar
    KRISH80
    Helper 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
      Super 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
        Helper 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 ?