Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

19 REPLIES 19
ClaudioL
Helper IV
Helper IV

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.

juagarmar4
Frequent Visitor

you could use: 

 

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

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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/



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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/



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

It helped me a lot! Worked like a charm in my report.

 

Thanks!

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 ?

Anonymous
Not applicable

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

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.