Seeing sum for the last day of the month only for all dates of the month

[afaro]

I have a table something like this: 

Date	Product	Value
1 Jan 2020	A	10
1 Jan 2020	B	20
2 Jan 2020	A	20
2 Jan 2020	B	30
3 Jan 2020	A	40
3 Jan 2020	B	50

I want to see tables like this

Date	Last Aggregate Value
1 Jan 2020	90
2 Jan 2020	90
3 Jan 2020	90

90 is the sum of the last value on the month. 
If we are on the current date i.e. today then I want to see the value for sum on today

[v-kongfanf-msft · Accepted solution]

Thanks for the reply from @vicky_ , please allow me to provide another insight:

Hi @afaro ,

If you want to present this result as a table. You can refer to the following formula in calculated table:

result = 
VAR LastDateOfMonth =
    LASTDATE ( 'Table'[Date] )
VAR LastAggregateValue =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        'Table'[Date] = LastDateOfMonth
    )
RETURN
    SUMMARIZE('Table','Table'[Date],"Aggregate Value",LastAggregateValue)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vicky_]

Try something like this: 

Last Aggregate Value = 
var lastDateOfMonth = CALCULATE(MAX('Table'[Date]), ENDOFMONTH('Table'[Date])) // get the max day per month
return CALCULATE(SUM('Table'[Value]), ALL('Table'[Date]), KEEPFILTERS('Table'[Date] = lastDateOfMonth))