cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CahabaData New Contributor
New Contributor

Re: Trailing 12 or Rolling 12 month sum

Here is posted the final code syntax - - definitely @parry2k got me going on the correct method - but for a viewer going thru this string needing the same solution - the code that works is slightly different:

 

SalesPersonTrail12 = CALCULATE(SUM(Table[Total]),
 DATESBETWEEN(
    Table[ActualDate].[Date],
  NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(Table[ActualDate].[Date]))),
  LASTDATE(Table[ActualDate].[Date])
 ))

 

....there are 2 things - note that there is a SUM ...that was necessary....and then the intellisense required I select .Date after each entry of Table.ActualDate     not sure if this is a new feature as I don't remember that from the past and older q/a doesn't show that either....one could select .Month or .Year - its not a feature I'm totally familiar with inside DAX

www.CahabaData.com
medwards807 Regular Visitor
Regular Visitor

Re: Trailing 12 or Rolling 12 month sum

Thanks for the follow up dax code that worked for you. I am working on something similar and had a question, I used your dax code and it did calculate the sum correctly, however it did for all dates, so I was wondering how you ended up with a rolling 12 months? did you filter by a date range? I was thinking the date between would have resulted in a rollng 12 month period?

 

Thanks 

dentonblake Regular Visitor
Regular Visitor

Re: Trailing 12 or Rolling 12 month sum

Ross-

 

Very helpful measure!  How could I modify to calculate the previous trailing 12-months?  My existing trailing 12-months is 12/2016 - 11/2017; trying to calculate 12/2015 - 11/2016 so I can compare.

 

Thank you, ~db

Super User
Super User

Re: Trailing 12 or Rolling 12 month sum

I would use 

EBIT Per FTE 12 Mth Avg = VAR CurrentEndDate = LASTDATE('Dim - Date Table'[Date])
VAR PreviousEndDate = DATE(YEAR(CurrentEndDate) - 1, MONTH(CurrentEndDate), DAY(CurrentEndDate))
RETURN

CALCULATE( 
	if(
		countrows(values('Dim - Date Table'[YearMonth])) = 1, 
		[EBIT Per FTE], 
		AVERAGEX(
			values('Dim - Date Table'[YearMonth]), 
			[EBIT Per FTE]
		)
	),
	DATESINPERIOD(
		'Dim - Date Table'[Date], 
		PreviousEndDate, 
		-12, 
		MONTH
	)
)

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


juagarmar4 Frequent Visitor
Frequent Visitor

Re: Trailing 12 or Rolling 12 month sum

you could use: 

 

lastdaycurrentmonth = DATE(YEAR(TODAY())-1,MONTH(TODAY())+1,1)-1

jausting Frequent Visitor
Frequent Visitor

Re: Trailing 12 or Rolling 12 month sum

So can we please see your code for the resolution ?

orangeatom Regular Visitor
Regular Visitor

Re: Trailing 12 or Rolling 12 month sum

Hi there,

 

Similarily to you I have a report which uses a year and month as a filter. In my report i have the YTD sales and the Prior YTD sales however i cannot get the rolling 12 month ytd sales.

 

When i select 2018 and 01 being January 31 2018, and implement the code you provided, it doesn't include the sales for January 1  2017 - December 31 2017.

 

SALES 12 Month =
CALCULATE( sum('TABLE'[SALES]),
FILTER FOR SALES1,
FILTER FOR SALES2,
DATESBETWEEN(DimDateInvoice[DateFull].[Date],
SAMEPERIODLASTYEAR(LASTDATE(DimDateInvoice[DateFull])),
LASTDATE(DimDateInvoice[DateFull])
)

 

I have confirmed that the SAMEPERIODLASTYEAR(LASTDATE(DimDateInvoice[DateFull])) variable returns Jan 31, 2017 and the LASTDATE(DimDateInvoice[DateFull]) returns Jan 31, 2018

 

orangeatom Regular Visitor
Regular Visitor

Re: Trailing 12 or Rolling 12 month sum

For those whom need additional support like I do, this thread helped me out as well.

 

https://community.powerbi.com/t5/Desktop/Total-amount-in-last-12-months-based-on-a-selected-month/td...