5 ways to calculate last 12 months in DAX for Power BI
HTML-код
- Опубликовано: 20 июл 2024
- 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:
gorilla.bi/dax/5-ways-to-calc...
Master Functions and Syntax in M
powerquery.how
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
Any videos are made free of charge. You can support my channel by giving a donation through: paypal.me/rickmaurinus.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
ruclips.net/user/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#PowerQuery #Last12Months #BIGorilla
This is super good. You the various versions can be applied in so many other ways. Thanks for sharing!!!
Great content. Easy to listen to and understand.
Thank you for sharing this video. Everything works very well.
Great tip! The option 1 is best for me since I would prefer simplicity
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?
What ease ! How effective ! 👍
Thanxsssss a lotttt Rick The Programmer
Thank you for the valuable info , could you show how to create IndexYearMonth column ?
Great Video. Thanks
Thank u for that very helpful, if I add some slicers for customers or products it will work this way or not ??
Learned so much 🎉🙏🙏🙏
Hey - nice video! I have an issue where if I use a date filter/slicer for my 12 month measures, it won’t show the proper row value for the given date.
So to clarify if I have values for a row on every month except March 2020, and I chose March as my start month based on the filter, the row will be empty, but the subtotal/total will be correct. If I chose April 2020, it will properly show the value for the last 12 month period.
Any suggestions on how to fix this?
Thanks, great video. These are nice ways to calculate a moving average. Can you use a "SELECTEDVALUE" in stead of a hard coded 12 ?
Hi Bart,
You sure can. If you for example want to use a slicer for the period selection, that would work great!
Do you have a video using slicers where you can 6 months or 3 months dynamically
great tutorial
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.
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.
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 ?
Thankx for great info. One thing pls suggest, in my table last month having date level data, and due to this 1 to 12 showing month level and last month showing day level sales in visual, can u pls make video on this ?
Hi Rakesh. I'm not sure I understand your scenario
hello sir , it really helpful video, can you please explain how to create Indexing - for month - Please Please create such video
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.
Did you get a solution?, i have the same requirement
¡Thanks!
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!
Hi, How did you generate de IndexYearMonthn Colunm?
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] )
)
)
Hey Jazzista,
Seems like a great approach! The use of the variables is optional. It only makes things easier to read. But if you prefer doing it without and it doesn't get overwhelming, please do so 😁
The index column I've added in Power Query using the 'Add Index column' . However, it's important to add the index column on the right level in the hierarchy. So when the data is grouped on Month level. Keep looking out for the next released video. Coming week I will release a video on adding index columns at the right level of the hierarchy.
It may point you in the right direction.
Rick
Thanks Rick. Look forward to that video. Thanks for replying.
@@jazzista1967 You can find the video I mentioned right here: ruclips.net/video/Hc3d8rMSXcQ/видео.html
Hope that helps :)
Hi Rick! Thanks for the video!! I want to show last 12 months from the last month of multiple months selected in slicer. I have faced an issue that the trend shows only selected months irrespective of last 12 months. Can you please help how to achieve this?
Hi! The methods shown only adjust the calculated values. But not the months on the x axis. For that you need a different approach. Check out ruclips.net/video/wLPvYq82TN4/видео.html
@@BIGorilla Thanks for your reply Rick... So in this case I need another calendar table to show last 12 months from selected months.
@@mohanr9522 yes. And I published this before I knew about a better solution. If you're comfortable with complex code also have a look at
www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/
I use this nowadays!
Cheers
Rick
@@BIGorilla Thanks Rick!!!
how about last ? weeks e.g. last 10 weeks, last 12 weeks etc.?
Please share the PBIX file for same.
if it is last month Sales why the values are cummulating like 469824+43421=513245, can u clarify my doubt
Each month shows the sales for only that particular month. But the idea behind this trick is that it shows the last 12 months on the axis of the graph. It's not so much about the calculation in each month, but more the months shown on the axis. Does that help?
why we have added +1 in edate function
Hi
Is it Same logic working for last 3 months trends in line chart
Absolutely!
@@BIGorilla pls make one video for last 3 months trends percentages in line chart
@@pavankumarreddy189
You can use the first 4 methods shown on my blog and in the video (gorilla.bi/dax/5-ways-to-calculate-last-12-months-in-dax/)
Just replace the 12 months by 3 months and you will have your required formula ready. Hope that helps!
Rick
I can't find a link to video how to do a IndexMonthYear in Power Query . It is possible to do without merging the tables ?
Hi. Yes that's possible. Easiest would be to group your data by year and month (also include the all details column), then sort the columns year and month. You can then add an index column. And lastly expand the detail row column. Et voila! Hope that helps
@@BIGorilla Thank you ‚ I will try - I like the way you explain things , clear and approachable !
🙏🙏🙏
Which measure is the fastest?
Hi John, that's a good question. The answer is, it depends on the model. Only testing the code using DAX studio will give the definite answer.
You can record the timing of each query. When you include the measure it should give you a good idea !
what if i want the result as an average?
is this cumulative total or last 12 Months sales?
It's regarding the last 12 months sales :)
@@BIGorilla But here the values are not showing previous year sales when we compare it showing only cumulative total
How not to show those month with no data?
Your screen is not visible proaparly, Plase keep it visible , tq
Hi! Thanks for the feedback. In which part do you have trouble seeing the screen?
@@BIGorilla I got first way , but reamaing ways I couldn't see it exactly
@@salidandigopi4358 I'm sorry to hear that. Please check if you're watching with the highest quality. Are you perhaps watching on a phone. It looks sharp on my end.
^^Rick
@@BIGorilla tq , I can see now clearly ,
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.
The data is of fund valuation. and across different categories across the last 5 years.
I could get the last 2 months values using measure 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