5 ways to calculate last 12 months in DAX for Power BI

This video you learn 5 ways in which you can calculate the last 12 months in DAX. The goal is to master different ways to reach the same result.

Which method is your favorite?

WRITTEN BLOGPOST:

Master Functions and Syntax in M

0:00 Introduction
0:45 The scenario
1:05 Version 1: DATESINPERIOD
3:14 Version 2: DATESBETWEEN
5:25 Version 3: Using an Index Column
8:12 Version 4: MAXDATE and EDATE
9:44 Version 5: LASTDATE and SAMEPERIODLASTYEAR

ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.

SUPPORT MY CHANNEL

SUBSCRIBE TO MY CHANNEL

LET'S CONNECT:

Thank you for your support!

#PowerQuery #Last12Months #BIGorilla

[thomasg]

This is super good. You the various versions can be applied in so many other ways. Thanks for sharing!!!

[teigenxayden]

Thanks for your share, it`s supper good methods. Each method is a way to understand different functions. thanks!!

[tranbinhquan]

Great tip! The option 1 is best for me since I would prefer simplicity

[philipberthiaume]

Thank you for sharing this video. Everything works very well.

[markhenderson]

Great content. Easy to listen to and understand.

[eminenceenduro]

Hi, Your content is really awesome, I need your help, how to calculate the last 12 months excluding the current month any thoughts or lead in this ?

[krishnamurthy]

Thank you for the valuable info, could you show how to create IndexYearMonth column ?

[srenbroner]

Thanks for the video - how do I not show, the periods after the lastest period with data. In my case i have 0 value for rest of the year, as i for now only have JAn data.

[nevermore]

Thank u for that very helpful, if I add some slicers for customers or products it will work this way or not ??

[ranjeetkarande]

hello sir, it really helpful video, can you please explain how to create Indexing - for month - Please Please create such video

[BestICan]

Can you do a video where a date in a disconnected table (Say, an incentive program ) needs to filter a separate fact table 12 months prior to the disconnected table date and then Sum a quantitative amount? I've tried and I can't get the correct filter context working. Thanks!

[abetarabein]

This is really great .. thank you so much for all your content. Can the same be done to graph the last 4 quarters based on a selected date on slicer?

[jazzista]

Rick: here is another approach but maybe it is too verbose . I am suing the next day function with datesbetween. Version number 3 in your example is very elegant with the used of variables. Now, How do you create a index month column in your date table? That was incredible. Thanks for sharing.



CALCULATE (
[Total Sales],
DATESBETWEEN (
'Calendar'[Date],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) ),
LASTDATE ( 'Calendar'[Date] )
)
)

[ankitsharma]

why we have added +1 in edate function

[WindyFebbiayuni]

what if i want the result as an average?

[sandeepmenon]

Hello, thanks for your video. I have an issue. I'm trying to calculate the Rolling 12M and what I need when it comes to the months of rolling 12 is, so for example if I'm looking in Jan 2022 then my 12 month previous should be Jan 2021 -Dec 2021 not Feb 2021-Jan 2021. Ideally, in the 12M the current month should be excluded. I used your first formula but I tried to used variables and DateADD functions to replace the MAX, but no luck. Any advice would be very helpful. thanks in advance.

[albertoguzman]

Hi, How did you generate de IndexYearMonthn Colunm?

[jhazielpetersen]

how about last ? weeks e.g. last 10 weeks, last 12 weeks etc.?

[idolsensei]

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. In short in one sheet, example if I filter to Jan 2022, the last 12 month's data will show but on the other section or table, only the Jan 2022 data which was filtered will show.

[barttitulaerexcelbart]

Thanks, great video. These are nice ways to calculate a moving average. Can you use a "SELECTEDVALUE" in stead of a hard coded 12 ?