cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

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

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

1 ACCEPTED SOLUTION
Community Support

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,

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

2 REPLIES 2
Community Support

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,

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

Super User

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))``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors