cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CahabaData New Contributor
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.....

www.CahabaData.com
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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.






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

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





View solution in original post

17 REPLIES 17
Highlighted
Super User
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. 

 

I hope it is helpful. 


Cheers,

P






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

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





CahabaData New Contributor
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

 

 

 

 

www.CahabaData.com
Super User
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/






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

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





Super User
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/






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

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





CahabaData New Contributor
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]

 

www.CahabaData.com
Super User
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.






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

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





View solution in original post

Ross73312 Super Contributor
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.

 


   

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

Proud to be a Datanaut!


   


CahabaData New Contributor
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....

 

must admit the NEXTDAY clause still has my head spinning

 

 

www.CahabaData.com
CahabaData New Contributor
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.....

www.CahabaData.com

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: 58 members 1,209 guests
Please welcome our newest community members: