cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Memorable Member
Memorable Member

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

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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

18 REPLIES 18
Helper I
Helper I

Good morning dear,

I am wanting to create a graph that shows me Rolling 12 and another one for the YTD, not being able to reach the required result.
I was googleing and tried several ways to create the metrics and columns to get the result, but when I put the R12 formula in the graph, it brings me the filtered month, and not what I need the filtered month and 11 months ago. For example: JAN 2016 filter and I want the graph to show me the totals for each previous month, until the month FEB 2015, that is, to show me 12 columns.
Could someone show me the columns and metrics that must be created to reach R12? You need step by step how to get to the final metrics of R12 and YTD.

From already thank you very much.

Greetings.

Frequent Visitor

you could use: 

 

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

Super User III
Super User III

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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

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

So can we please see your code for the resolution ?

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.

 

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

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

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

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

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

 

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

 

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 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors