Sum of entire selected (last) month

[raymond]

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

[parry2k]

@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

[raymond]

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 🙂

[v-piga-msft · Accepted solution]

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

[raymond]

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