cancel
Showing results for
Search instead for
Did you mean:
Helper IV

## 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_end Total Sales 29/02/2020 200 31/03/2020 1000 30/04/2020 200 31/05/2020 300 30/06/2020 100 31/07/2020 50 31/08/2020 10 30/09/2020 2 31/10/2020 700 30/11/2020 25 31/12/2020 60 31/01/2021 300

How do I achieve that?

1 ACCEPTED SOLUTION
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.

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

5 REPLIES 5
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.

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

Helper IV

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

Helper IV

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

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/
Resolver II

Hi @bml123 ,

Please refer to these posts:

Thanks,

Dheeraj

## Helpful resources

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors