Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
raymond
Post Patron
Post Patron

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

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.

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

@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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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 🙂2019-03-21 08_28_20-PowerPoint-Bildschirmpräsentation  -  Präsentation1.png

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.