Help with measure to get the "last amount with a value" from a date list

[Telstar]

Hi All

Have a measure issue that I cannot solve and need your help
I have the following table of values

Date	Code	Amount
31/03/2022	200	100
31/03/2022	250	1000
30/04/2022	200	100
30/04/2022	250	2000
31/05/2022	200	100
31/05/2022	250	3000
30/06/2022	200	100
30/06/2022	250	4000
31/07/2022	200	100
31/07/2022	250	5000
31/08/2022	200	100
31/08/2022	250	6000
30/09/2022	200	100
30/09/2022	250	7000
31/10/2022	200	100
31/10/2022	250	8000
30/11/2022	200	100
30/11/2022	250	9000
31/12/2022	200	100
31/12/2022	250	10000
31/01/2023	200	100
28/02/2023	200	100
31/03/2023	200	100
30/04/2023	200	100
31/05/2023	200	100
30/06/2023	200	100
31/07/2023	200	100
31/08/2023	200	100
30/09/2023	200	100
31/10/2023	200	100
30/11/2023	200	100
31/12/2023	200	100

What I need to accomplish is a "total to date" for code 200 and the last value for code 250
So from the above it would be
31/12/2023 - 200 - 2200 ~ sum of all the values for code 200
31/12/2023 - 250 - 10000 ~ last value for code 250

I am struggling with the DAX to get the 10000 amount - I have tried CALCULATE(SUM(AMOUNT), MAX (DATE),CODE="250") but obviously that gives me 0 - what am I missing.

Help much appreciated

Thanks

Telstar 

[AlB · Accepted solution]

Hi @Telstar 

Try this measure:

Measure = 
VAR lastDate_ =
    CALCULATE (
        CALCULATE ( MAX ( Table1[Date] ), Table1[Code] = 250 ),
        ALLSELECTED ( Table1 )
    )
RETURN
    CALCULATE (
        DISTINCT ( Table1[Amount] ),
        Table1[Code] = 250,
        Table1[Date] = lastDate_
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Telstar]

Thanks for the quick solution @AlB - worked perfectly

Much appreciated.

Telstar