Calculate new value for max date of each month

[Anonymous]

Hi,

I want to calculate new column which will be the sum of all amounts for the max date of each month. Eg. In below data, 31st Jan is max date in 'Jan' month. Summation of all 31st Jan amount is '150'. I want to calculate 'New Amount' column. Would really appreciate of anyone can help me.

Thanks!

Date	Amount	New Amount
1/29/2021	10	150
1/30/2021	20	150
1/30/2021	30	150
1/31/2021	40	150
1/31/2021	50	150
1/31/2021	60	150
2/2/2022	70	90
2/3/2021	80	90
2/10/2021	90	90

[ryan_mayu · Accepted solution]

@Anonymous 

you can create a column

Column = 
VAR _maxdate=maxx(FILTER('Table',YEAR('Table'[Date])=year(EARLIER('Table'[Date]))&&month('Table'[Date])=MONTH(EARLIER('Table'[Date]))),'Table'[Date])
return sumx(FILTER('Table','Table'[Date]=_maxdate),'Table'[Amount])

or create a measure

Measure = 
var _maxdate=maxx(FILTER(ALL('Table'),year('Table'[Date])=year(MAX('Table'[Date]))&&month('Table'[Date])=month(max('Table'[Date]))),'Table'[Date])
RETURN sumx(FILTER(ALL('Table'),'Table'[Date]=_maxdate),'Table'[Amount])

pls see the attachment below

[sevenhills]

Try this:

Latest Date In Month Amount = 

var _MM = Month(SELECTEDVALUE('Table'[Date]))
var _YYYY = year(SELECTEDVALUE('Table'[Date]))
var _MaxDateInMonth = CALCULATE( Max('Table'[Date]), FILTER( ALL('Table'),  Month('Table'[Date]) = _MM && Year('Table'[Date]) = _YYYY ))

RETURN SUMX(Filter(ALL('Table'), 'Table'[Date] = _MaxDateInMonth ), 'Table'[Amount])

Output:

Note: I added year also part of the logic.

Say, If you dont want the year and only month, use the below in which I removed the year. 

Latest Date In Month Amount 2 = 

var _MM = Month(SELECTEDVALUE('Table'[Date]))
var _MaxDateInMonth = CALCULATE( Max('Table'[Date]), FILTER( ALL('Table'),  Month('Table'[Date]) = _MM))

RETURN SUMX(Filter(ALL('Table'), 'Table'[Date] = _MaxDateInMonth ), 'Table'[Amount])