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

Ross73312 Super Contributor
Super Contributor

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

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 57 members 1,121 guests
Please welcome our newest community members: