Return Max Date if Value Is 0 Per ID

[Anonymous]

I would like a DAX statement that returns the MAX(Date) for each ID only if the Amount=0 for the record with the MAX(Date) value.

End Date = CALCULATE(MAX(SampleData[Date],ALLEXCEPT(SampleData, SampleData[ID])

gets the MAX(Date) for each ID, but obviously doesn't account for the Amount being 0. I am not sure whether I need an IF statement or a FILTER (or both).

Thank you!

SampleData

ID	Date	Amount	End Date
1	2/1/2020	500.00
1	3/12/2020	0.00	3/12/2020
2	1/11/2020	300.00
3	1/4/2020	255.00
3	2/15/2020	0
3	3/22/2020	14.00
4	1/21/2020	51.00
4	2/16/2020	0
4	3/17/2020	0	3/17/2020

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this code for calculated column:

End Date =

VAR _lastDate = CALCULATE(MAX('Table'[Date]); ALLEXCEPT('Table'; 'Table'[ID]))

RETURN IF('Table'[Amount] = 0 && 'Table'[Date] = _lastDate; _lastDate ; BLANK())

If you consider it as a solution, please mark as a solution and kudos.

Ricardo