DAX for Power BI - Last 12 Months from Selected Date (Trailing 12 Months TTM)

Hey guys! Parker here. In this Power BI tutorial I will show you how to show the previous 12 months of data when the user clicks on a single date selection. This is kind of hard to explain so let's jump into the demo!
Link to PBIX:
www.dropbox.co...
Enroll in my introductory or advanced Power BI courses:
training.bieli...
Elite Power BI Consulting:
bielite.com/
Data Insights Tools:
www.impktful.com/
Connect with me on Twitter!
/ powerbielite

[@zhin13]

Had given up trying to do this and then got this video recommended. Thank you!

[@imarmodels]

This really is a brilliant easy to follow explanation of a problem that has been driving my a bit crazy. Thank you!

[@BIElite]

Glad to hear that, Ian! Thanks for watching

[@NeoDragonKnight]

Been looking for a solution to this all day long, and this is the one that worked and listed out in an easy to understand way.

[@timscongack5867]

Parker, thank you so much for posting this. It helped me with a measure I needed to create literally the day after you put the video up - saved me tons of time. Really appreciate these videos!

[@BIElite]

Haha that's great to hear! Very fortunate timing

[@pradeepreddyn]

The dates are incorrect with the formula : DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 12, DAY ( CurrentDate ) ) . It might return 12/31 for some, 12/1 for the next month, it also messes up with February dates. Use DATEADD or EDATE(CurentDate, )

[@yweekendshooter3701]

I first saw the Month(CurrentDate-12), and this is the king.

[@amitshukla189]

Thank you so much for this video. You have no idea how much happy i am after finding this solution.

[@BIElite]

No problem, Amit!

[@canhuang7325]

Thanks a lot for this, I have watched many videos for this metrics but yours are best and easy to understand!

[@anastasianikolovski8337]

Thank you very much for posting this super useful tutorial. This basically helped me understand the concept of disconnected tables and how powerful it works for such type of calculations in PBI.

[@shreyashkejriwal7842]

Hey, How to do it for multiple visuals? Like, if I select date at one place, It changes all visual present at dashboard according to it.

[@jessicawiley3136]

Sorry for writing you, just out of curiosity your page come up on my suggested friend lists so I was just wondering if I knew you from somewhere?!!!

[@amandawang6592]

I want to give you a thousand thumbs up! I spent two days searching this way and then I find your video! Thumbs up! Thumbs up!

[@BIElite]

Awesome! Glad to hear it Amanda!

[@jj000071]

Hey, thanks for sharing this video. Now I want one common date filter to filter my LTM Measure we have created and for FactInternetSales data, how do I do that ? Whenver I apply any filter option from FactInternetSales it doesn't give th desired result for LTM.

[@luiztargino2153]

It worked and it is so simple compared to other solutions I have tried. Thank you so much

[@jessicawiley3136]

Sorry for writing you, just out of curiosity your page come up on my suggested friend lists so I was just wondering if I knew you from somewhere???

[@timosborn73]

A-mazing, thank you so much, I was trying to do this but was driving me nuts, so glad I found your video.

[@susanandedkeshen1630]

i followed the steps to create the date table but the measure table did not automatically appear. When I click Create New MEasure, it shows up as a second field in the date table not as a separate table. How did you do that?

[@BIElite]

Hey! Check out this video to learn how to create a measure table: kzbin.info/www/bejne/bHqrf4x6rsSMpNk&t=

[@chiragshah1426]

what happens when you have months that do not exists in fact table... (for pervious 12 months) I have this issue and I see blanks for entire SUM

[@whoisthewanderer]

Thank you but it is not clear what to do if there are other visuals that still has to be filtered to that date, which is most often the case.

[@umasankarthellapuri5560]

Woowww it's really superb, excellent explanation. Awesome logic driven... 👏👌

[@jessicawiley3136]

Sorry for writing you, just out of curiosity your page come up on my suggested friend lists so I was just wondering if I knew you from somewhere???

[@SkippyClyro]

The download link wasn't working so I thought I'd share the DAX here... Great content btw TTM_Sales = VAR CurrentDate = MAX ( 'Date'[End of Month Date] ) // Based on the calculated table DISTINCT(FactInternetSales[End of Month Date]) VAR PreviousDate = DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 12, DAY ( CurrentDate ) ) // Change - 12 to any value for a smaller/larger range. VAR Result = CALCULATE ( SUM ( FactInternetSales[SalesAmount] ), FILTER ( FactInternetSales, FactInternetSales[End of Month Date] >= PreviousDate && FactInternetSsales[End of Month Date]

[@BIElite]

Thanks for this, Will! I've fixed the link so hopefully it works for everyone now.

[@dmitrikochubei3569]

THANK YOU! Like you can't even imagine how much of a THANK YOU that THANK YOU is.

[@jessicawiley3136]

Sorry for writing you, just out of curiosity your page come up on my suggested friend lists so I was just wondering if I knew you from somewhere???

[@dmitrikochubei3569]

@@jessicawiley3136 Hi there, I don't think we know each other. I guess KZbin Algo found some association between our accounts.

[@jessicawiley3136]

Oh...I'm Jessica Wiley from Palm 🌴🥥 Beach 🏖️⛱️ Gardens Florida 🇺🇲, I am a professional market analysis and expert binary trader 📈

[@jessicawiley3136]

How about you and where are you from?

[@idolsensei7]

Hi, Thanks for sharing this. I do have one question. What if I have other data sections but doesnt need to show the 12months roll? I know the disconnected calendar works to show the 12months roll but what if in other sections of the dashboard I need to show the figures on that specific month only that was filtered.

[@lalitagrawal8329]

Thanks for this Video. But when no Date is Selected in the Slicer then Graph is showing blank I tried to show from the MAX(Date) through IF condition still not getting the answer Can you please Help Me!!

[@aminegatou4492]

Hello, great tutorial , but I wanna do the same thing but not last 12 month but last N Months from selected month ( for exemple choosing July so I need all month from january to July ) how can I achieve this?

[@taylorgreenfield3036]

How could you use this same formula to show a straight trend line for average or standard deviation? I'm having trouble getting my AVG and STD DEV measures to give me that straight line over the selected months.

[@CarlosMontilla]

Great explaination! You made it super easy to follow. Thanks

[@BIElite]

Glad to hear, Carlos!

[@mohitgarg360]

Why to create date table separately. Cant it be implemented using same date column which already exists?

[@tanushree6216]

Hi Parker, how to display last 12 weeks of data from selected date.

[@TheJimiCat]

Hey Parker, Quick query please Followed the DAX formula and have working fine Thanks! But how do I get it go in Year/Month Order? As in if I select March 2019 I would like March to be 1st then Feb, Jan and then Dec18 etc. Currently my graph will stay Jan to Dec. Thanks again.

[@producer1e]

Amazing video! This helped me a ton. However, how do you connect the TTM month slicers to other date filters in the dashboard? I understand that the TTM month slicer is not related to any of the tables, but was wondering if there is a workaround to have 1 slicer to control all date filters in the report.

[@kostaszogo21]

That's exactly what i'm looking for! Did u manage to find a solution to that?

[@Wolfman01969]

This is great but is there a way to make it display 12 months opposed to 13? My client looks at 12 months.

[@davidmagyari6781]

Hi, Thanks so much for the video it is really helpful, although it is not working for me. Could be a problem if I have a date dimension table, what is referenced in the filter? thanks

[@arayow]

Any idea on how to get the Last Years TTM? SAMEPERIODLASTYEAR seems to be not working or I am not doing it correctly.

[@eswarsonu1115]

Hi , Need your help. I want to create a column with month name + number of days in a month. Number of days in a month should change when we select month and year from the filter. Some cases we have leap year in that February month should show 29 and like wise non leap year February month should show 28 days. Output should be in this way . Jan 31 Feb 28 Mar 31 Apr 30 When I select year as 2020 from filter output should be like this Jan 31 Feb 29 Mar 31 Apr 30. Thanks in advance.

[@hello-u2w]

Thats great, but what happens when you have a table full of measures.... I'd like a slicer (month Year) when selected just bring back the past 13 months

[@arshiyashaikh4816]

I din't understand what is the Measure table? It has column1 in the table? Is it a calculated Table?

[@paulcoles6152]

Good Video. I need to do (nearly) exactly this. But if there are no sales for a month within the 12 month period I need to show a blank column

[@SoNJFF]

Hi guys, great stuff. Is there a way to show prior months 13-24 using same code? I have tried using -12 and -24 for bands, but it does not seem to want to collar months 13-24. It does start at month 24, but it does not end, I need it to end at month 13.

[@gunnerjensen5998]

a bit of a long shot but I'm trying to create a very similar type of graphic. I'm trying to sum up the last 12 months of sales to be shown by month. Then next to that trying to show the values for each month but -1 more year. Currently my -1 year section only shows the months that have already occurred this year so Jan-May. I'm unsure why the other months are blank. Would I be able to share some content to see if it's a simple miss on my part?

[@Edgar-qp3wg]

you save my day ! , but im still wondering ... because whenever i try with DATESINPERIOD formula i cant retrieve 3 last month moving average considering my date have a day granularity...

[@arunshenoy9260]

Hey, Thanks a lot for sharing this video. Much appreciated! It was really helpful. I tried implementing the same with my data & the output was accurate!

[@jessicawiley3136]

Sorry for writing you, just out of curiosity your page come up on my suggested friend lists so I was just wondering if I knew you from somewhere?!!!

[@allonewordlowercase]

G'day Parker. This is awesome. I need a little more help - can't quite crack it. Can you please advise how I can use the same filter for something that just requires one month. I've been able to copy the formula in some instances and use it as a filter on some visualisations with great effect. But on the page I also need to show something for the selected month only. Cheers Jim

[@heidiemiliaholappa]

Very good tutorial, saved it for later use! Loved seeing how you write and test measures, very informative!

[@BIElite]

Thanks Heidi! Yes, testing measures through variables, although tedious, is definitely a time-saver in the end.

[@kevinlabore1726]

Thanks Parker good example of trailing month. Although the potential uses & setup might be a little confusing.

[@BIElite]

Sorry for the delayed response, Kevin. I agree that this is a pretty specific use case and not necessary for everyone. Thanks for watching!

[@kevinlabore1726]

@@BIElite to clarify I did NOT mean your example was confusing but the application of the concept might be to some

[@BIElite]

For sure, I agree with you! It's one of those scenarios that most people probably won't care about until they realize why they might want it (or are asked for it).

[@gondebas]

There are two weird things. First, your final bar chart shows 13 bars, instead of expected 12. The other thing is calculation of VAR PreviousDate. You got the result by odd approach. Why not using time intelligence functions like DATEADD? Or if you have to, why not subtracting 1 from Year, instead of subtracting 12 from Month? I am surprised it works. I would expect for say April the result 4-12=-8. Anyway, I like your idea with additional table.

[@zacharydoolittle7939]

I am seeing the additional bar charts when changing to a 6 month. For some months I show 7 months on the bar chart others I show 6. I haven't ran into that issue with the 12 month though. 6 Month Trailing Bookings = var Currentdate =MAX('Rolling Date Table'[DATE]) Var previousdate =DATE(YEAR(Currentdate), MONTH(Currentdate)-6,DAY(Currentdate)) Var Result = CALCULATE( SUM(Bookings[Bookings]), FILTER( Bookings, Bookings[DATE]> previousdate && 'Bookings'[DATE]

[@gopinath3396]

Hi , This is not working for me . when i use 2 different fact tables

[@ahmedal-dossary4386]

Exactly what I was looking for today. Thanks a lot for the video!

[@jessicawiley3136]

Sorry for writing you, just out of curiosity your page come up on my suggested friend lists so I was just wondering if I knew you from somewhere?!!!

[@neoandprissy]

Hi. This works for my graphs but doesn not work for my matrix. Anybody knows why?

[@santiago6184]

I'm in the same problem here... big problem

[@ferorozco7547]

Awsome video and dax skills - I have one issue in this moment, how can I calculate the total sales of 1 year, if I have populate it two different years in one table?? Like "Total Sales 2013".

[@cesarmoreno8549]

This is great, but how would you do a cumulative TTM? I want to smooth the data and take out any seasonality.

[@BIElite]

I think what you're referring to would be a slight variation from my "12 Month Rolling Average" video. Check it out here: kzbin.info/www/bejne/aqHHq5uBg9yEors&t=

[@coollehavre]

thanks but how to display month bar even if i have NULL value ?

[@jessicawiley3136]

Sorry for writing you, just out of curiosity your page come up on my suggested friend lists so I was just wondering if I knew you from somewhere???

[@pradeepkrishna5095]

The Syntax for')' is incorrect is the error im getting

[@turnbullca1]

Hi Parker, this is absolutely brilliant. I love how the way you explain quite complex stuff and how you think generally Love your work!! If I wanted to do this for Previous Year and add that measure on say okviz Bullet Chart as a Comparison what would that measure look like. Also same for YTD and YTD LY. Any help greatly appreciated. Thanks Chris

[@adityachopra6247]

Thank you so much for the useful information. It helped me a lot.

[@BIElite]

Glad to hear, Aditya!

[@kentlindgrempyml704]

Great tutorial and useful function. Thank you. It would be great to twist it à bit; to present a cumulative result. I’ve tried to achive cumulative actuals in bars and budget in line presentation for the last 12 Months. Any ideas? BR Kent

[@musicmeloaj1597]

Just wondering can't we do the same using Last and anchor options in out of box relative slicer for date?? Without writing this Dax piece.

[@BIElite]

+Music Melo AJ you can from today's date. The purpose of this is to show the last 12 Months from any date you select

[@riteshprasadsingh6029]

MAX is not working. it is taking max date by default

[@mmungsse8473]

Thank you so much for your knowledge sharing! this helps a lot. One quick question here, why did you have to create the new table to define date again? I already have a date table which have relations with others and when I tried to connect date table to the existing one, error occurred and couldn't find the reason

[@rajpalpatil274]

To select one date from list of date by default if there a relationship between the date table the specific date sales will show up.So to get our desired request to get previous data we needed a extra date table which has no Relationship with the sales data. Hope it helps , suggest you watch the video again its specifically confirmed why we need to create the extra date table.

[@stargazerlse]

Does anyone know how to add 3/6/12month average lines on top of this graph?

[@sheetalnair2298]

Thanks a lot for posting this.. it was very helpful and very well explained

[@jessicawiley3136]

Sorry for writing you, just out of curiosity your page come up on my suggested friend lists so I was just wondering if I knew you from somewhere???

[@mynameisyuriorlov]

Hello @BI Elite, this video is pretty cool and it helped me a lot in consulting mission 1 year ago. Right now, the logic is perfect but I would like the Previous Date to be based on the last Value that exists, not the Last Date. Do you think it is possible ? Many thanks in advance, and best regards, Augustin de la Fouchardiere, MYPE

[@vasudevpatil2309]

I am facing one challenge where I wanted to show the Current month value than Previous month value and then % change between these for multiple categorical variables, in columns in Table/Matrix visualization. I could get the last 2 months values using measure as you suggested above but then when I tried to get the % change in visual, it appeared for both of the months side by side and not after the 2 columns. I Want to create visuals like below e.g. Fund Category||Fund name||Nov2021||Oct2021||%change Next month it would automatically change to Fund Category||Fund name||Dec2021||Nov2021||%change

[@danieldainty3523]

Thanks heaps - this is really clear and concise and exactly what i was after. Liked and Subscribed. Keep up the videos cheers !

[@jessicawiley3136]

Sorry for writing you, just out of curiosity your page come up on my suggested friend lists so I was just wondering if I knew you from somewhere?!!!

[@sann5146]

This seems very useful for trend analysis, thanks

[@Sarabjitsethi]

Can you give a Dax which you wrote ... directly here .. it will be easy for us to use

[@BIElite]

Hi Sarabjit, please use this link to download the PBIX: www.dropbox.com/s/stqp6wlooa7ozcq/TTM.pbix?dl=0

[@sekharreddy6949]

Thank you for this video and i need last 2 years sales by clicking one year can you do that please , thank you

[@dunder64]

This one is great. Where could I get the AdvantureWorksExcel.xls file you are using as your data source? I've been looking for it, but looks like its only as db files, and I hoped I didn't need to set up a db for this.

[@BIElite]

Peder, send me an email at [e-mail] and I can send it to you.

[@Beard1974]

This is great, and really helpful - thank you very much. But what about leap years? If you're on the 29th Feb, there wasn't one 12 months ago.

[@Beard1974]

Got it... VAR PreviousDate = EOMONTH(CurrentDate, - 11) (The - 11 instead of - 12 brings back 12 months rather than 13; alternatively leave it - 12 and change the >= to just >.)

[@BIElite]

Nice! I like your solution. I wasn’t thinking about leap year when I was making this video.

[@lalitagrawal8329]

@@Beard1974 Thank you But when no Date is Selected in the Slicer then Graph is showing blank I tried to show from the MAX(Date) through IF() condition still not getting the answer. What can I do to achieve this

[@JoelNove]

Great measure! How do you account for leap year for VAR PreviousDate? Thanks!

[@jessicawiley3136]

Sorry for writing you, just out of curiosity your page come up on my suggested friend lists so I was just wondering if I knew you from somewhere???

[@usmanlatif1365]

Great. It was really helpful. Thankyou

[@BIElite]

Great to hear, Usman!

[@MJNGaming1]

Hey, @BIELITE (BI Eilite) Really lovely video :) Use this measure in my own setup. Just have one question: I really want to have the average for the past 12 months, so a 12 month period of time, next I want the another 12 month period but just 12 months after the first want started. So it will start from 24 month ago and go 12 month forward in time. Most import if possible to not make those 2 measures overlap. If that isn't possible it's will still be useful. Atm I have done the measure for the past 12 month, but couldn't figure out to do the other one I mention, hope you can help Kindly regards Martin

[@eyesontheball6018]

Hi, how to do it when the slicer is a slider?

[@BIElite]

You would have to specify if you wanted to use the FirstDate or LastDate within the measure.

[@eyesontheball6018]

@@BIElite thank you!

[@eyesontheball6018]

@@BIElite Sorry, could you explain me how to do it? It doesn't work when I try to put LASTDATE instead of MAX in the measure. Thank you for your help!

[@sohailansari9337]

Great.. Thanks where can I download this pbix file from?

[@BIElite]

+Sohail Ansari I've been meaning to start a Dropbox location where I'll put all of the pbix's. I'll let you know when I set it up!

[@yaolanjin100]

this is awesome!! thanks so much for the clear explanation! just one thing: your voice recorded is too low.

[@BIElite]

Hi Yolanda! Glad you enjoyed the video. I think this video was from before I had my new mic, so hopefully the newer videos are better!

[@yaolanjin100]

@@BIElite I like how you show the single month selection for past 12 month chart. However, I am struggling with using the same filter (if possible) to display MTD, YTD calculation numbers. Just wondering if you have videos on creating dashboards like that. But i will check out your other videos too! Thanks!

[@aryansena7290]

For last 9 months it's not working

[@venkatesht9048]

Hi, It's a good video, please keep it up.

[@BIElite]

Thank you!

[@vishalpawashe9487]

Nice Video Just wanted to know can we get the PreviousDate using the DAX formula DATEADD and shift it ahead by 1 year

[@ed2921]

Vishal Pawashe yes, but I’d use the datesinperiod function. It’s much easier to set max date and then -12 months. But Parker’s method is more customizable.

[@vishalpawashe9487]

@@ed2921 That's right agreed

[@BIElite]

Good points. I like the datesinperiod idea. I've often had trouble using DATEADD where the result returned blank for no reason. I just use the DATE function to be safe

[@vishalpawashe9487]

@@BIElite Would it be possible to throw some light on Row Context and Filter Context for evaluating the DAX functions. Have seen a few KZbin videos on it, but it gets confusing as I see more videos.

[@BIElite]

Sorry, lost track of this comment. I think that would be a great idea for a future video. I would need to make sure that I can explain it well enough to make it useful for others lol

[@jmfeal]

I think you should add a special condition for february 29, because the previous date won't exist

[@dhaniamamodaly9604]

Hello, thank you it works perfectly when I have one measure on the chart but when I insert 2 measures from 2 different table I don't have the right X axis (some months are missing). I put DimDate[Date] on my X axis and there is a relationship between DimDate and my 2 tables. Do you know how to fix this please? Thank you !!

[@dhaniamamodaly9604]

Actually my question is : does it work when the slicer is a DimDate? Because I would like to have only one date slicer on my dashboard.

[@lindabond9307]

Amazing - great tip, thanks

[@jessicawiley3136]

Sorry for writing you, just out of curiosity your page come up on my suggested friend lists so I was just wondering if I knew you from somewhere???

[@RSantiago_]

Thank you very much!!!

[@clarararahq4138]

still an amazing trick in 2024!!!

[@danotic]

YOU ARE A BEAUTY!

[@huiqingxu5286]

Thank you!

[@BIElite]

No problem! Thanks for watching

[@mynameisyuriorlov]

great !

[@BIElite]

Thank you!

[@hi_vishy]

Superb

[@BIElite]

+Vishesh Saxena Thanks!

[@THOMPALLYCultureFusion]

SPEAK LOUDER....PLS

[@BIElite]

Older video. See my new stuff.

[@hrandhisdata]

If I need to add same period last year for another bar chart to compare with current year, how can I adjust the formula for the same period last year data?

[@ThuThuyDo0403]

Super useful, thank you.

[@jessicawiley3136]

Sorry for writing you, just out of curiosity your page come up on my suggested friend lists so I was just wondering if I knew you from somewhere???

[@rogelioarturocastanedamart6204]

Thank you a lot!