LOOKUPVALE from the same table

[KRISH80]

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 · Accepted solution]

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

[KRISH80]

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]

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]

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]

HI @KRISH80 ,

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

Thanks,

Pragati

[Pragati11 · Accepted solution]

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 · Accepted solution]

HI @KRISH80 ,

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

Thanks,

Pragati