Getting 12 months running total based on excels Last Twelve Months

[Alex_Alex]

Hey guys, I'm new to powerBI and I can't figure out the solution to my problem. The probem is the Line chart X axis that is MonthYear is showing the separate amounts of each month, instead of the total. What I want to do is like this in excel.Excel LTM total displayed over 12 months

The excel above is always showing the total of last 12 months. So the total in January 2020 was like this over 12 months, and we see the total of the last 12 months from December was a little less, and so on.

Right now I want to show the total that is in the card at the Jan-2020, and below I am showing the total from December 2019 which is a little less. And I want to display this in the X axis line chart, just like in excel.

The measure I am using to get the 12 months sum is this:

Sales last N months = CALCULATE(SUM(InvoiceLines[SellPriceExclVAT]), DATESINPERIOD('Date'[Date], MAX('Date'[Date]), - 12, MONTH))

My problem is, instead of showing the total for that selected month in the line chart, it is splitting the sales into 12 months that create that 12 months back total. Any help would be really appreciated!

PROBLEM SOLVED! 

It is possible to solve this total calculation with the measure I have created with a relative date slicer.

The problem was I was using the invoiceDate from the fact table, not the actual date dimension. 

I took the Date attribute from the Date dimension and used it as a relative date slicer and everything works perfectly!

[amitchandak · Accepted solution]

You can use a relative date filter for that

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

Or do the same in visual level filter

[amitchandak]

Or try one of the three

 12 month =
 CALCULATE(SUM('Monthly Collection Report PBI'[Debit credit value])
	, DATESBETWEEN('Date'[Date], Min(dateadd('Date'[Date],-12,month)), MAX('Date'[Date])))
 
 12 month =
 
 var _min = Minx(allselected('Date'),dateadd('Date'[Date],-12,month))
 var _max = Minx(allselected('Date'),'Date'[Date])
 
  CALCULATE(SUM('Monthly Collection Report PBI'[Debit credit value])
	, DATESBETWEEN('Date'[Date],_min , _max))
	
	
12 month =
 
 var _min = Minx(allselected('Date'),dateadd('Date'[Date],-12,month))
 var _max = Minx(allselected('Date'),'Date'[Date])
 
  CALCULATE(SUM('Monthly Collection Report PBI'[Debit credit value])
	, filter(all('Date'),'Date'[Date]>=_min && 'Date'[Date]<= _max))

[Alex_Alex]

@amitchandak 

Sadly relative slicer and these measures did not produce the result I need. I keep getting the same thing.

The line showing separate values through the months that add up to the total, instead of showing 12 totals

[amitchandak]

Can you share sample data and sample output.

[Alex_Alex]

@amitchandak 

https://drive.google.com/open?id=1h4ewmgy2oVf24c45GPEGKH58agIyZp0m

Attached a pbix file with dummy data I tried to replicate. Try clicking through the months from january 2020 till may 2019 or something like that and watch how the total is displayed in the line. What I want is that when you click on jan 2020 slicer, it will display the total, but also display the total for the rest of the 12 months in the X Axis. So the end result should be a line displaying all 12 total months back, depending on which month you select in the slicer.