cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
raymond Member
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
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.

Untitled.png

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





Did I answer your question? Mark my post as a solution.

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





raymond Member
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 Smiley Happy2019-03-21 08_28_20-PowerPoint-Bildschirmpräsentation  -  Präsentation1.png

Community Support Team
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.

Untitled.png

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.
raymond Member
Member

Re: Sum of entire selected (last) month

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 304 members 3,296 guests
Please welcome our newest community members: