cancel
Showing results for
Did you mean:
New Contributor

## Trailing 12 or Rolling 12 month sum

trying to craft the correct DAX measure; data table/model is transacation per row/record: (snippet of 1 month)

Person - ActualDate - Amount

Jim            11/22/16     \$1000

Jim            11/23/16     \$1100

Jim            11/25/16     \$  900

Sue            11/01/16     \$1000

Sue            11/11/16     \$1100

Sue            11/21/16     \$1200

etc...many months/years/people

In a Table Visual - it easily offers a monthly sum:

Person - Amount - Month  Year

Jim            \$3000        11       16

Sue           \$3300        11       16

etc...many months/years/people

What is sought is a measure/column providing the prior rolling/trailing sum by person

Person - Amount - MonthYear - Trailing 12 Sum

Jim            \$3000        11   16         \$28,000

Sue            \$3300        11   16         \$29,950

etc...many months/years/people

looking thru time intelligence / dax patterns and haven't found the one that works....though I think I'm close - something is not clicking......

LATE EDIT: look at the end of the string to see the actual code I ended up using - with much thanks to @parry2k in getting me on track.....

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Trailing 12 or Rolling 12 month sum

you don't need to use monthname, that is only for average, but this formula you need to translate

```Sales12M := CALCULATE (
[Sales],
DATESBETWEEN (
Calendar[FullDate],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Calendar[FullDate] ) ) ),
LASTDATE ( Calendar[FullDate] )
)
)```

So sales in this case will be amount and full date will be replaced with date from your calendar table.

You don;t need to worry about person, once you add this on a visual with person, it will automatically group it by person.

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

17 REPLIES 17
Super User

## Re: Trailing 12 or Rolling 12 month sum

i usually have calculated column in calendar table called "Months from Today" using datediff between calendar date and current date (today date) and then you can filter where "Months from Today" > 12 and <= 0.

Cheers,

P

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

New Contributor

## Re: Trailing 12 or Rolling 12 month sum

thanks but not following

current date is not at all in play

sum of 12 months prior - rolling -----  by person  shifting with each person/month row.....

Person - Amount - MonthYear - Trailing 12 Sum

Jim            \$3000        11   16         \$28,000

Sue            \$3300        11   16         \$29,950

Jim            \$2900        12   16         \$31,000

Sue            \$3000        12   16         \$33,250

Super User

## Re: Trailing 12 or Rolling 12 month sum

aha, so basically you are looking for cummulative total for last 12 months at any given point. did you looked at this cummlative total formula, just add 12 month filter to it, if need further help, let me know and I will put together something. I believe you already looked at it

http://www.daxpatterns.com/cumulative-total/

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Highlighted
Super User

## Re: Trailing 12 or Rolling 12 month sum

i believe this will do it

http://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

New Contributor

## Re: Trailing 12 or Rolling 12 month sum

thanks, I was looking at that one as a model before my post....haven't quite gotten it to work....

part of the issue is a sum, not average, and by person rather than single column.... and don't think I'm translating his fields into my fields correctly:

Calendar[FullDate]

Calendar[MonthName]

Super User

## Re: Trailing 12 or Rolling 12 month sum

you don't need to use monthname, that is only for average, but this formula you need to translate

```Sales12M := CALCULATE (
[Sales],
DATESBETWEEN (
Calendar[FullDate],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Calendar[FullDate] ) ) ),
LASTDATE ( Calendar[FullDate] )
)
)```

So sales in this case will be amount and full date will be replaced with date from your calendar table.

You don;t need to worry about person, once you add this on a visual with person, it will automatically group it by person.

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Super Contributor

## Re: Trailing 12 or Rolling 12 month sum

Here is a measure i created to create a 12 month rolling average.  It could easily be modified to be a 12 month rolling sum as well.

```EBIT Per FTE 12 Mth Avg = 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],
LASTDATE('Dim - Date Table'[Date]),
-12,
MONTH
)
)```

This piggybacks off another measure that calculates my "EBIT Per FTE".  So here you could piggy back off any measure you are already using that you want to get a sum off.

Proud to be a Datanaut!

New Contributor

## Re: Trailing 12 or Rolling 12 month sum

first let me thank you for staying with this....much appreciated...

this file/report has a set of visuals and so far I've had no need for a Calendar Table - - and so being both curious and lazy I instead used the ActualDate field rather than setting up a Calendar Table....and all appears to be working....

New Contributor

## Re: Trailing 12 or Rolling 12 month sum

hey thanks - will file that too...  @parry2k had posted a solution I was just posting to it when your's came in.....

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 258 members 2,790 guests
Recent signins: