Sum of sales in the last 12 months

[bml123]

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?

[Jihwan_Kim · Accepted solution]

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

)

)

)

https://www.dropbox.com/s/7qz1c84n3zgncvb/bml123.pbix?dl=0 

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

[bml123]

@Jihwan_Kim 

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

[bml123]

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

[Ashish_Mathur]

Hi,

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

[dkaushik]

Hi @bml123 ,

Please refer to these posts:

1. Solved: Rolling 12 Months Sales - Microsoft Power BI Community

2. Solved: 12 Month Moving Sum - Microsoft Power BI Community

Thanks,

Dheeraj