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]),
....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
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?
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
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 ) )
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 =
FILTER FOR SALES1,
FILTER FOR SALES2,
I have confirmed that the SAMEPERIODLASTYEAR(LASTDATE(DimDateInvoice[DateFull])) variable returns Jan 31, 2017 and the LASTDATE(DimDateInvoice[DateFull]) returns Jan 31, 2018
For those whom need additional support like I do, this thread helped me out as well.