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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
bml123
Post Patron
Post Patron

Sum of sales in the last 12 months

Hi,

 

I have sales table with date and amounts for each sale and I want to show the last 12 months sales. I should be able to show the last 12 months sales data when the user selects a date from the slicer as below. 

 

Month_End
31/01/2021
28/02/2021
31/03/2021
30/04/2021

 

If 31/01/2021 is chosen, it should show the total sales for the last 12 months as below

 

Month_endTotal Sales
29/02/2020200
31/03/20201000
30/04/2020200
31/05/2020300
30/06/2020100
31/07/202050
31/08/202010
30/09/20202
31/10/2020700
30/11/202025
31/12/202060
31/01/2021300

 

How do I achieve that?

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @bml123 

Please check the below picture and the sample pbix file's link down below.

I suggest having a disconnected slicer table like below.

 

Picture1.png

 

Total Sales Slicer Select =
VAR slicerselect =
EOMONTH ( MAX ( SlicerTable[Date] ), 0 )
VAR oneyearagodate =
EOMONTH (
DATE ( YEAR ( MAX ( SlicerTable[Date] ) ) - 1, MONTH ( MAX ( SlicerTable[Date] ) ), 1 ),
0
)
RETURN
CALCULATE (
SUM ( Sales[Sales] ),
KEEPFILTERS (
FILTER (
ALL ( Dates ),
Dates[Date] > oneyearagodate
&& Dates[Date] <= slicerselect
)
)
)
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
Super User

Hi, @bml123 

Please check the below picture and the sample pbix file's link down below.

I suggest having a disconnected slicer table like below.

 

Picture1.png

 

Total Sales Slicer Select =
VAR slicerselect =
EOMONTH ( MAX ( SlicerTable[Date] ), 0 )
VAR oneyearagodate =
EOMONTH (
DATE ( YEAR ( MAX ( SlicerTable[Date] ) ) - 1, MONTH ( MAX ( SlicerTable[Date] ) ), 1 ),
0
)
RETURN
CALCULATE (
SUM ( Sales[Sales] ),
KEEPFILTERS (
FILTER (
ALL ( Dates ),
Dates[Date] > oneyearagodate
&& Dates[Date] <= slicerselect
)
)
)
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@Jihwan_Kim 

I want to show the same for the last 12 quarters.  Any idea how to do it?

@Jihwan_Kim it worked perfectly. You are excellent and a gem

Ashish_Mathur
Super User
Super User

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
dkaushik
Resolver II
Resolver II

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.