Power BI Dax to find max value date based on two other columns condition

[AH2022]

Hi All,
I have a Table Shown Below.  I need a Dax Formula that will look for IDofPledge (A2), through Column Linkedgift_id (B3:B7) and returns the Max(Date) of Column DateOfInstallment (C7) in the column InstallmentEndingOn (D2).

I have tried creating a Dax column using the Dax Code below, but it doesn't seem to work.  

How do I correct the DAX in order to compare A2 with B3:B7, and to bring C7 in D2 ?

I have scoured the web looking for an answer on this topic and I tried different Filter Arguments, with no success. What am I doing Wrong? Thank You!

InstallmentEndingOn =

Calculate(max(Table [DateOfInstallment]),FILTER(Table, Table [linkedgift_id]=[id]))

	A	B	C	D
1	id	linkedgift_id	DateOfInstallment	InstallmentEndingON
2	IDofPledge			2020-01-01
3	1 IstallmentPledgeID	IDofPledge	2016-01-01
4	2 IstallmentPledgeID	IDofPledge	2017-01-01
5	3 IstallmentPledgeID	IDofPledge	2018-01-01
6	4 IstallmentPledgeID	IDofPledge	2019-01-01
7	5 IstallmentPledgeID	IDofPledge	2020-01-01

[AH2022]

Hi AUDISU,

Thank you for your answer.

Here is a Print Screen.

1.Looks for [id] = 123456 within column  [linkedgift_id]

2.Calculates MaxDate within column [date] for [linkedgift_id]=123456

3.Returns MaxDate visàvis ligne [id] = 123456

Thank You.

[AUDISU]

Hi @AH2022 ,

Please try following DAX formula.

InstallmentEndingOn =

VAR IDNO = SELECTEDVALUE(Table_Gift[Id])

RETURN

MAXX(FILTER(ALL(Table_Gift),Table_Gift[linkedgift_Id] = IDNO), Table_Gift[date])

Thank you.

[AH2022]

Hi AUDISU,

It works when used as a measure. How could I transform this code to create a calculated column please?

I understand Ineed to convert filter context into row context.

Thank you very much.

[Anonymous]

Hi @AH2022 ,

You can create a calculated column as below to get it, please find the details in the attachment.

InstallmentEndingON = 
CALCULATE (
    MAX ( 'visàvis ligne'[date] ),
    FILTER (
        'visàvis ligne',
        'visàvis ligne'[linkedgift_id] = EARLIER ( 'visàvis ligne'[id] )
    )
)

Best Regards

[AH2022 · Accepted solution]

Thank you @yingyinr

InstallmentEndingONColumn =

CALCULATE (

MAX ( Table_Gift[date]),

FILTER ( Table_Gift, Table_Gift[linkedgift_Id] = EARLIER ( Table_Gift[Id]) )

)

Thank you @AUDISU

InstallmentEndingOnMeasure =

VAR IDNO = SELECTEDVALUE(Table_Gift[Id])

RETURN

MAXX(FILTER(ALL(Table_Gift),Table_Gift[linkedgift_Id] = IDNO), Table_Gift[date])

[AUDISU]

Hi @AH2022 , 

Could you please show me current data table view and expected view.

Thank ou.