topic Re: LOOKUPVALE from the same table in DAX Commands and Tips

LOOKUPVALE from the same table

KRISH80 — Wed, 08 Apr 2020 08:47:46 GMT

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'

Re: LOOKUPVALE from the same table

KRISH80 — Wed, 08 Apr 2020 09:06:47 GMT

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.

Re: LOOKUPVALE from the same table

Pragati11 — Wed, 08 Apr 2020 10:48:29 GMT

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

Re: LOOKUPVALE from the same table

KRISH80 — Wed, 08 Apr 2020 11:26:29 GMT

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 ?

Re: LOOKUPVALE from the same table

Pragati11 — Wed, 08 Apr 2020 11:44:07 GMT

HI KRISH80 ,

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

Thanks,

Pragati

Re: LOOKUPVALE from the same table

Pragati11 — Wed, 08 Apr 2020 15:14:58 GMT

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

Re: LOOKUPVALE from the same table

Pragati11 — Wed, 08 Apr 2020 15:47:46 GMT

HI KRISH80 ,

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

Thanks,

Pragati