This is the best solution for Rolling KPIs using a filter selection for month that I've seen. Thanks Alberto
@charlenemarch3997Ай бұрын
Literally a lifesaver I have been messeing around with about a hundred measures, measued columns filters etcfor the past 2 days and this worked like a charm. THANK YOU!!!!
@dillonpettigrew32415 ай бұрын
Thank you. I love that you move through the example step-by-step to help us understand how the calculation works.
@YagobaKopa Жыл бұрын
What a Couple! Always start up looking everywhere and always end up in the Italian place. Thanks So Much.
@Cellyzaan2 жыл бұрын
This video was incredibly useful! I was trying to figure out how to have a dynamic KPI visual that has a rolling 12 month graph based on date slicer selection and this did the trick. Thank you very much!
@christofferchamoun211 Жыл бұрын
How did you manage to Solve the rolling 12 sum? When you say rolling sum, do you mean the rolling sum for each month or does the rolling sum only for the months that are present in the visual? 😊
@rogeriodornellas21158 ай бұрын
This is a great solution! I'm using and it works very well, the only issue is if there is no value in a period, it will skip the month instead of showing 0, in a table the result is even worse with a missing month, but thank you anyway! 😎
@carlosaugustodelcastillona11052 жыл бұрын
I applied this solution using a Sparkline for a KPI Card. It's really helpful and useful for my company. Thanks Alberto!
@Moon_Rise6393 Жыл бұрын
I was wondering from past 2 days everyone explained but not explained every single steps to perform. Thank God you save my time and i got the solution now. Thanks a ton!!
@preetamjawaria4413 жыл бұрын
Damn !! Thank you very much. Thrilled to understand the reason behind every single step.
@champk99622 күн бұрын
This is literally a life saver technique. Thank you soooo much for uploading such a great content
@sumanth167823 жыл бұрын
WOW!! explained in such detail so the implementation is absolutely clear - AMAZING Alberto!
@MeirbhinOConaill2 жыл бұрын
This is wonderful. We struggle with Current month reports as using dates will hide PriorMth on the first few days of the month, where business wants to see PM sales until the day after the first Sales day to CurrentM. I do with the a [LastDayWithSales] measure & a binary date table calculated column as a filter. But this UX requires to unfilter the binary [Current month] filter then choose a date range. Your solution here is fantastic. Thank you so much
@vincerocchi9083 Жыл бұрын
This is Fantstic Alberto!! Thank you for sharing! Very clear with excellent demonstration of steps and the (interim) results we should expect along the way. I have been looking for something similar (a 10 year rolling average) for a few weeks and you’ve answered a ton of my questions. I should be able to do what I need to now!!
@davidjkyang3 ай бұрын
Thank you for sharing in such a detailed step by step explanation. It is brilliant and it worked perfectly.
@gonqq31403 жыл бұрын
Once again.. Brilliant. You and your channel were my 2021's best find and I don't think anything will top that. Thank you !
@namangarg70232 ай бұрын
Sir u r god of SQLBI...tks for contributing towards your small learners like me..
@Grundraak7 ай бұрын
Grazzie Alberto, for insurance loss ratio reporting with premium and claims and a loss ratio % this is an amazing solution. i spent most of the past 2 months trying to build this with a slicer and then trying to set dates back etc. i have stopped chasing, and can start implementing !!
@PowerEliteStudio3 жыл бұрын
Thank you very much Alberto, some time ago I had to solve this scenario, however, this approach is much better and more elegant Amazing
@lukesteen1573 жыл бұрын
Genius solution. This saved me so much trial and error! Thank you, Thank you, Thank you!!!!!
@davidtawa83383 жыл бұрын
Holy moly, thank you very much, you deserve more viewers, awesome content with amazing explanation!!
@PeterKontogeorgis3 жыл бұрын
Your solutions are so elegant. Amazing work.
@mcscunha11 күн бұрын
EXCELENT! I loved this explanation.
@saharlatifi3510 Жыл бұрын
Always finding solutions here! Thank you Alberto!
@TafadzwaMundida7 ай бұрын
Informative as always. One question if you would be so kind, how should I modify the code to work with a previous year sales measure?
@leonsommers46483 жыл бұрын
Thanks very much Alberto / SQLBI. I've been wanting to solve this conundrum for ages and here it is. Thank you for your clear explanation
@workstuff87712 жыл бұрын
Very grateful for this instructional video! It bugs me that PowerBI makes things that should seem so simple seem so complicated. In Tableau, things like this are accomplished in much less steps and with also very limited coding
@ubonguttah2 жыл бұрын
Nice one. Always a Joy to see you breakdown DAX in simple terms
@kot232 жыл бұрын
Excellent video, so intructional and well-explained. Filter context is examined deeply and the reason for each filter manipulation becomes clear. This pattern will be certainly used with some modifications, thank you for this vid!
@siddheshamrutkar86843 жыл бұрын
Superb.. I cracked it what I was looking for and it was for only month end dates.. Great One Alberto.. 🙏👏
@GGGMONEY5559 ай бұрын
Thank you so much I've been looking for that for a loooong time and you are the first explaining it properly. Looks like i'm going to watch all your videos ;)
@deepakgupta31923 жыл бұрын
Great Video , Great Explanation , love it . thank you to come online and sharing . love all your videos ...Love Dax
@damionc3 жыл бұрын
Wow. This solved my issue. Can I say WOW once again. You are the best.
@abdulrehman562 жыл бұрын
Awesome man ... you guys are simply Ninjas of Power BI
@rohithkothaneth Жыл бұрын
Awesome Alberto!! Thank you for explaining it so clearly and providing step by step explanation. If I want to show '0' for all the blank COUNTS across the last 12 months, is it possible? I tried to include COUNT('Cases'[Number]) + 0 also tried IF(ISBLANK('Cases'[Number]) ), 0, ('Cases'[Number])) in the CALCULATE section, but it is displaying all the months and not giving only for the last 12 months data set. It is not giving me the desired output.
@samdresiksamdresik9 ай бұрын
@rohithkothaneth Did you find a solution for this problem?
@slimmammou8932 жыл бұрын
Alberto you are my hero, One favor : how it is possible to put in the same chart the YTD value, the last 3 months, the last 6 WEEKS & the last WTD day by days.
@vijayansilambarasan96292 ай бұрын
Thank you so much! I followed your steps, and it works like magic. However, I have one small issue. For example, I set the view to display last 12 months, but sometimes 1 or 2 months do not have any data. I still want to visualize those months. How can I achieve this? Please advise.
@kejmil19922 жыл бұрын
You save my project at work! Thank you for this, well done!
@SivaKumar-hg4ps3 ай бұрын
Thank you for the great lecture sir. Sir, I have one doubt. Above this, it is very useful to see month-wise. I tried the above concept month-wise and it successfully worked. But if I filter by 'Date[Date] in sales amount by date wise in chart, it does not work. Will we be able to find date-wise information for the above concept, sir? For example, if I clicked June 2024 in Slicer, the table showed the last 3 month values. If I filtered by 'Date'[Date] in the date-wise sales amount in the chart, the matrix table did not show the date-wise filtered value. It shows only that month wise Value . Kindly, give me solution for this concept Sir.
@helloworldtt92715 ай бұрын
marvelous!!! but why do i only see past 11 mths data when i also sort by another location slicer? thanks in advance for your advice!
@ashishmohan47073 жыл бұрын
Thank you Alberto, DAX Champion. :) This Channel helps me a lot. You are champion on DAX and post o through with your channel i am trying to get all your knowledge I have one query
@KMF7955 Жыл бұрын
This is a super video!! I implemented it as best as I could, however, the 6 months & associated values are showing correctly, however, I'm getting all of the other months also, but they are showing as zero. Any tips on what could be happening would be greatly appreciated.
@nareshkosuru15338 ай бұрын
Glad I found this video and am a big follower of your videos and articles. In this video exercise, how to bring MoM and QoQ based on months or quarters displayed when period is selected by the user. I am getting MoM measures for every month column which is wrong. Only latest months or quarters to be considered. My fiscal period is in rows.
@sairam293310 ай бұрын
Excellent Explaining @alberto. can we group this to a Sales_Agent (Say) level ?. By following the approach in ethe video i'm able to get on a whole; when trying to break about at sales-agent level i'm able to see only the months when he made any sale; but looking for all the last 6 months even if there is no sale from him. thanks in advance
@johansantacruz64643 жыл бұрын
Awesome! Solutions to actual customer's requests.
@Navsworld133 жыл бұрын
Wait I didn't understand at 9:50min mark when you were debugging why you expected 184 rows. Can you explain that? Thank you in advance. Love the videos. Learned so much.
@giangutierrez5109 Жыл бұрын
Thank you for this tutorial. I tried to use this showing previous days, it worked but it is including weekends. How can I skip these days so it won’t show on the graph?
@justinwduff Жыл бұрын
Excellent video, thank you so much! wondering if you are able to share way to show the previous 3 months and future 3 months... where your selected value is the "pivot" column of the chart. For example, I'd like to be able to see the revenue targets from the past N months and also the revenue targets for the next N months.
@leo2life3 жыл бұрын
Grazie mille per la tua spiegazione! As always, excellent! Greetings from Brazil!
@ArnaudSCHAER Жыл бұрын
that's very helpful, thank you. HOWEVER. there's a big red warning flag you should mention, it caused me headaches : using group calculations not only REQUIRES you to not use automatic-measures on your visuals, or it will simply not work. But also, as soon as a group calculation is added to the model, automatic-calculations are no longer accessible for a new visual, requiring the user to ...... create a measure for every single data he wants to visualize !
@SQLBI Жыл бұрын
It is mentioned in our trainings and articles! www.sqlbi.com/calculation-groups/
@Anculka963 ай бұрын
@@SQLBI Hello, are you aware of any other solution for showing last x months based on user single slicer selection, please? I do not use total, but amounts diveded per category and for that this solution by copying date table unfortunately does not work
@sww58082 жыл бұрын
Fantastic, the explanation of filter context is great. Thanks!
@Sabrina-kq6en5 ай бұрын
Holla Aberto, great explanation 👍But can you please explain how to create this slicer for the whole report 🙏 I try it since days 😢I Was not able to adopt it for the whole report - I hope you can help 🤗 A slicer where i chosse the Month and get the data from this month - 6 month.🥀
@stephenellis85547 ай бұрын
This worked after checking out ~5 other solutions and made sense.
@FredrikLindblad-yk2ul Жыл бұрын
Excellent solution! I do have a question if you have a solution for if I for each month would like to show the sum of the Last 12 months i.e. like a trailing LTM for the last 12 months still only selecting a single month.
@mariammamporia53922 жыл бұрын
Thank you alberto, You literally saved me ! Keep the great work.
@markanthonyarasa23962 жыл бұрын
Hello Alberto, thanks for this very informative video! Just a question, you're displaying a calculated data in your example (Total Sales). I was trying to display volume (Distinct Count) per month to which I cannot implement the "CALCULATE" function. Would you be able to suggest what should I do?
@humayunshahzad9181 Жыл бұрын
Incredibal .... Amazing Alberto.. Many thanks.. It helps alot in my project.
@iira13112 жыл бұрын
Thank you for the wonderful explanation. love your all videos but my Date field hierarchy gets removed which is creating problem for slicer, Any solution on that?
@bentatlim2 жыл бұрын
Ciao Aberto, your video is amazing. All of them. Thank you for that. But i have a question. Instead of a fix month (here 6) i want the sale of the previous month of that year. This means. I choose in my slicer for example Mai 2022. And my Digramm should show me Jan 2022, Feb 2022, Mar 2022, April 22 ,Mail 2022 ....or i choose Mar 2022 , then my Digramm should show me Jan 2022 , Feb 2022 and Marc 2022. I would be very thankful, when you can solve my problem. Kindly Regards from Berlin.
@SQLBI2 жыл бұрын
This video was about a specific issue, it seems you need time intelligence calculations: www.daxpatterns.com/standard-time-related-calculations/
@vasudevpatil23092 жыл бұрын
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
@bostjanbrumec71742 жыл бұрын
Thank you Alberto. The same solution works for the new sparkline feature!
@mchopra19893 жыл бұрын
Thanks for this useful video. Brilliant solution!! It works perfectly for me and best thing I got it at the right time. @Sqlbi Hope you will have a solution for the simple yet hard to find solution in DAX. I have a matrix table with metrics at the row and week ending date (calendar table) at columns. I want the latest week to come at the start but power BI put it in default ascending order. Please suggest. Thanks again!!
@GSMonkey8 ай бұрын
Great! Thank you for the solution, chatgpt wasnt much help here, i guess its too complicated :D Did it with weeks instead. Now the next problem is finding out how to make a "year to selected week" with the same slicer, where the year can change depending on which year the week belongs to.
@GSMonkey8 ай бұрын
Got it to work, if anyone should have the same need. Just an edit of the code from the video. Again thank you, would never have solved this alone. m__offeredYearToWeek = VAR RerefenceDate = MAX( 'Calendar'[date]) VAR FirstOfYear = DATE(MAX('Calendar'[weekBelongsToYear]),1,1) VAR NumberOfDays = INT(RerefenceDate - FirstOfYear)+1 VAR PreviousDates = DATESINPERIOD( 'Calendar previous'[date], RerefenceDate, -NumberOfDays, DAY ) VAR Result = CALCULATE( [m__callOffered], REMOVEFILTERS( 'Calendar'), KEEPFILTERS(PreviousDates), USERELATIONSHIP( 'Calendar'[date], 'Calendar previous'[date]) ) RETURN Result
@gFowmy3 жыл бұрын
Thanks for another great video! The link for the "Article and download:" in the video description is not clickable, please fix the link.
@SQLBI3 жыл бұрын
The article will be published in a week, the video was wrongly scheduled, it will be back next week.
@enl32992 жыл бұрын
I used field parameters (Mth/Qtr/FY) on Category of waterfall chart, and the out-of-the-box experience is that it would not show 'waterfalls' from beginning up to the first data point set on the axis/category. Eg. When 'Qtr' is selected, chart will plot 4 data points on the axis, and then draw 3 sets of waterfalls between the 4 points (duh~). The first set of waterfall to depict the movements from beginning of period up to the first data point would NOT be shown, contrary to 'normal user expectation'. Certainly not a bug here but it is such a wet blanket to not have easy to implement dynamic axis parameter on the waterfall chart. Thankfully the technique here enabled adding a period dynamically to the front of whatever periods have being selected to serve as a 'beginning' axis point. Thus when user select a year, the measure would consider 1 additional period prior user's selection. For details sake, the measure i used was a balance-todate that accumulates cash movement to show a balance at the last day of selected month. Hopefully this comment helps someone struggling with the same issue, particularly with waterfall chart.
@hosseinmortazavi55473 жыл бұрын
thanks for great video. one more simple solution is just create a column in date table that return 0,1 if date column is less than 6 month from selected date.
@SQLBI3 жыл бұрын
If you use a calculated column, it would be static, the user would have no way to select the starting point
@Deepak-iq5ul2 жыл бұрын
Thank you for such videos❤. Although I have some different scenario, I have YYYYMM slicer on my report page with multiple years of data.And data is loading on monthly basis. Currently I have saved YYYYMM slicer with recent 12 months selected but client is expecting if new month data gets loaded again recent 12 months should be automatically updated. Any input on this please.
@rogervandecraen97972 жыл бұрын
very well explained video, is it possible to apply the same technique for the last x weeks
@2bquest3 жыл бұрын
This is exactly what I needed in my current project. Thanks Very much.
@onamissionteam93732 жыл бұрын
Best teacher ever
@AmitSingh-er3lk3 жыл бұрын
Thank you so much.. Do we have video for complete Mquery/measures
@drod963 жыл бұрын
I currently use a technique like this when providing financial reports, I am also having the data presented by our companies fiscal year and crop year to date CYTD and FYTD from the respective cost centers. The challenge I have is showing the "future" months as well to look at rolling forecast as well as total Budget. I dont use calculation group, and my model right now requires about 5 measures just to give me FYTD based on a current date or any date you wantto type in and it re-calculates the measures based on that date. Thank you for this video. Could you show how you would implement this, but in a companies fiscal year to date measure ( powerbi knows the first month of the fiscal year regardless of what date you set as input?). Thanks again.
@herrjerr95272 жыл бұрын
Thanks Alberto. Well explained and easy to implement. I have one issue though. I have noticed that if one or more months in my N period does not have data, that particular month is not shown in the chart. If I set "Show Items with no data" to ON, then it ignores the Previous N months to show filter and shows all months (months less than (current month - N months) and months greater than current month). Could you help?
@sushimayonnaise83122 жыл бұрын
Same issue! Do we have solution for this?
@sabrinalimage1663 жыл бұрын
Thanks for a great video Alberto! I was wondering if this could be used to only show data for every 6 months before a date for a past X amount of years? For example company A’s most recent date is 6/1/21 and looking to see sales on 12/1/20, and 6/1/20, but company B’s most recent date is 3/1/21 so sales 6 months from their date would be on 9/1/20 and etc. If not possible to use this type of example, do you have a similar video? Thanks!
@grytskokonstantin1272 Жыл бұрын
Incredibly useful. Thank you very much!
@KrishnaKumarKundoly3 жыл бұрын
Thanks Alberto. I was using sqljason's method for this purpose but it fails if we want to show values from 2 different tables. Your method works brilliantly. I used what-if parameter for the previous N months selection.
@lalainerepuela59327 күн бұрын
Just came across this video and found it very helpful. Can the calculation group be made as well in Power BI alone without using Tabular Editor? Thanks
@SQLBI26 күн бұрын
Yes, now it is possible, you can do that with Power BI Desktop using the model view. See powerbi.microsoft.com/en-us/blog/deep-dive-into-the-model-explorer-with-calculation-group-authoring-and-creating-relationships-in-the-properties-pane/
@hakim40002 жыл бұрын
Alberto as always you re a Dax Lord
@armstrongabebrese83102 жыл бұрын
great explanation...how about if you selected multiple months
@LuisMunoz-pi3nc2 жыл бұрын
Hi Alberto - Thank you for sharing this training - I have a question how did you create your Date Table? I have used the Calendar option and it doesn't work. Could you share in your article how you created your Date Table to have this filter selection work.
@SQLBI2 жыл бұрын
We have several DAX patterns for that: www.daxpatterns.com/time-patterns/ And you can simply use Bravo to create one: bravo.bi
@ricardorb61132 жыл бұрын
Thanks Alberto. But, could this be applied to a rolling measure?, Thanks
@sankhadeepchakraborty25523 жыл бұрын
Great solution, I am using to show last 3 quarters for all my measures, but is it possible to show last 3 years YTD before the 3 quarters like this dynamic column based on user month enddate selection 2018-12,2019-12,2020-12,2021-03,2021-06,2021-09. Thanks.
@geirforsmo87492 жыл бұрын
Very Nice video. We have an almost identical case but in our case we use a month table that is related to the fact table. So the dim_month and fact table have a key yyyymm but also a endofmonth date. So If we want to do the same ss in the video we need to rewrite the Logic here. We want 12 month backward from a selected year month. Perhaps it is possible to get a date without selecting the period slicer like last date/period with data
@Mago_Legend6 ай бұрын
You’re amazing! Thank you.
@erickknackstedt31313 жыл бұрын
Need this for some custom tooltips. Thank you!
@nataliiaiatsenko77793 жыл бұрын
Thank you very much! Your explanation is perfect. Exactly what I need.
@bravucod3 жыл бұрын
You make it look so easy!!!
@RobertoStaltari Жыл бұрын
Another milestone! Thanks!
@sherif_rufferson3 жыл бұрын
Hi. Thx for this video! But... Is there any chance to aply this with two slicers? (one for year and the other the month) Thanks in advance!
@beginho24543 жыл бұрын
Thank you Alberto, DAX Guru. :) This Channel helps me a lot.
@soniaseudie74 Жыл бұрын
I love your videos! Such a good teacher! Thank you!
@jithiljohn29612 жыл бұрын
Thanks Alberto, instead of year-month slicer, I want to use date range slicer. And this formula is not working in this case. Can you please help
@Bharath_PBI3 жыл бұрын
Thank you. Curious to know the reason for shadowing date table, creating inactive relationship and then bringing it back in the measure? With single date table, capture selected date, ignore the filter coming from slicer and calculate amount for last 6m based on selected date, returning blanks for any other months. Finally apply a visual level filter to overcome blanks. Also noticed that calculation groups mess the report when there are other visuals on the page. Isselectedmeasure to be used additionally. Appreciate such videos 👍. Thank you
@przemekkoska833 жыл бұрын
Ciao Alberto Thanks for this video! Great work as always. I am using this a lot when showing trend graph however I never duplicated the calendar table. Instead, I was using order date/delivery date from the sales table formatted as month year or yyyymm and then dragging it into x-axis. My measure looks very similar for example: CALCULATE ( SUM ( 'Sales'[Quantity] ) , DATESINPERIOD ( 'Date'[Date] , MAX ( 'Date'[Date] ) , -13 , MONTH ) ) Is there any disadvantage of this solution? All the best, Przemek
@Zealand-Data6 ай бұрын
it seems like I run into trouble, when I syncronize with slicers from other pages in the report. Is that something that you have experienced, or have any good advice on? Otherwise it has worked perfectly
@ChrisSmithFW2 ай бұрын
Fantstic! Thank you.
@lisashoff7777 Жыл бұрын
amazing, worked perfectly
@MrThanasis3803 жыл бұрын
Great video, thanks for sharing. Can this techique be applied in direct query? I am looking forward to your reply.
@SQLBI3 жыл бұрын
Yes, but as usual expects worse performance in DIrectQuery.
@MrThanasis3803 жыл бұрын
@@SQLBI Thank you Alberto. It worked flawlessly.
@victorjbm052 жыл бұрын
Alberto, please, help me (sorry for my bad English). What changes do I need to filter all days of this month if I have a day slicer?
@paulbrown18023 жыл бұрын
Very interesting. Can you explain why TREATAS doesn't work if it's used instead of USERELATIONSHIP?
@khushbujaiswal66848 ай бұрын
This is great, thank you for your help.
@thorichikawa96873 жыл бұрын
Thanks for the video Alberto! I used the same logic to calculate the value of the last 7 days and it worked just fine. However, I would like to only show the working days of those 7 last days. Is that possible? Can you help me? Thanks again!
@SQLBI3 жыл бұрын
You may consider using a column with the working days sequential index and filter over that: www.sqlbi.com/articles/counting-working-days-in-dax/