cancel
Showing results for
Did you mean:
Highlighted
Member

## Sum of entire selected (last) month

Hello community,

I wish to calculate the sum of the entire last month that is selected by a date slider.

Hence if December has 100 Sales and Novemeber 200 Sales and you adjust the date slider:

1. from 05.november to 05.December, the sum should show 100 Sales from december
2. from 30.november to 30.december, the sum should show 100 sales
3. from 10.december to 15.december, the sum should show 100 sales
4. from 05.november to 30.november, the sum should show 200 sales

I have attempted a few approaches, one of which looked like this. The result werent very pleasing.

```Sum Sales Last Month from Slider =
CALCULATE(
[Sales];
DATESINPERIOD( 'Date-Table'[Date] ;
LASTDATE( 'Date-Table'[Date] );
STARTOFMONTH( LASTDATE( 'Date-Table'[Date] ));MONTH ))```
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Sum of entire selected (last) month

Hi @raymond ,

For your requirement, you could try the measures below.

```total sale each month =
CALCULATE (
SUM ( 'data'[Revenue] ),
ALLEXCEPT ( data, 'data'[Date].[Month], data[Date].[Year] )
)
Sum of entire selected (last) month =
VAR maxdate =
MONTH ( MAX ( 'data'[Date] ) )
RETURN
CALCULATE (
[total sale each month],
FILTER (
ALLSELECTED ( data ),
MONTH ( 'data'[Date] ) = maxdate
&& YEAR ( 'data'[Date] ) = YEAR ( MAX ( 'data'[Date] ) )
)
)
```

Here is the output.

More details, please refer to my attachment.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User

## Re: Sum of entire selected (last) month

@raymond sorry your request is not fully clear

You asked sum of last month and based on your examples below it suppose to be different result

Last month for following 1-3 items would be November, isn't it? and result would be 200 and for item #4 last month will be october and no previous month sales based on your example data of nov and dec.

1. from 05.november to 05.December, the sum should show 100 Sales from december
2. from 30.november to 30.december, the sum should show 100 sales
3. from 10.december to 15.december, the sum should show 100 sales
4. from 05.november to 30.november, the sum should show 200 sales

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Member

## Re: Sum of entire selected (last) month

I guess the terms I used have room for interpretation.

You are absolutely right. I am looking for the sum of the selected end month. Perhaps this image will clarify my request

Community Support Team

## Re: Sum of entire selected (last) month

Hi @raymond ,

For your requirement, you could try the measures below.

```total sale each month =
CALCULATE (
SUM ( 'data'[Revenue] ),
ALLEXCEPT ( data, 'data'[Date].[Month], data[Date].[Year] )
)
Sum of entire selected (last) month =
VAR maxdate =
MONTH ( MAX ( 'data'[Date] ) )
RETURN
CALCULATE (
[total sale each month],
FILTER (
ALLSELECTED ( data ),
MONTH ( 'data'[Date] ) = maxdate
&& YEAR ( 'data'[Date] ) = YEAR ( MAX ( 'data'[Date] ) )
)
)
```

Here is the output.

More details, please refer to my attachment.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Sum of entire selected (last) month

@v-piga-msft: Thanks, it seems to work properly. Great solution by the way.

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 304 members 3,296 guests
Recent signins: