cancel
Showing results for
Did you mean:
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

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

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 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
)
)```

Proud to be a Datanaut!

Frequent Visitor

## Re: Trailing 12 or Rolling 12 month sum

you could use:

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

Frequent 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

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

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 57 members 1,121 guests
Recent signins: