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!!!!
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!
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? 😊
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!!
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 ;)
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! 😎
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!!
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 !!
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
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!
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
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
Very good solution. Could i use this approach to show values from the first month of the selected year to selected month? For example choosing 2024 in year slicer and October in month slicer getting the values from January to October and show values in matrix visual, monts values in their own columns.
HI....... Can anyone tell me how to show last Six month sales with discontinue month & also I want to show a month which has no sales as a null in last six month filtering?
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?
Hi all, When I try to make the deactivated relationship between the two calendar tables (Many to one), my calendar tables loses the date format, so Time Intelligence is not working. In the video, after the relationship is created, both tables keep the date format but in my case, the format is lost. I can set manually one calendar table with Date/time column format but the second one, I am not able to do it. Anyone knows why is this happening? Thank you
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.
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.
I know this is a older video, but does anyone know what formula he used for the Year Month Number? I cannot get it to sort correctly. I feel I have spent more time on the sorting issue than the entire visual...
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.
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.
Hi I need to count 6 month data from prior month and I tried to use this Dax it don't work for me! I have two table Date and Date2.can you help me to find solution? thank you
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.
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.
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
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.
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.
Hi...How can we subtract the variables in calculation group.. VAR currmoth=..... VAR lastmonth=..... RETURN currmonth - lastmonth The subtraction is not woorking.... Pl help.....Thanks
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?
I have the same requirement. but i need to use the calculated column instead of measure [sales Amount]. Since i am using calculated column It says table of multiple values passed. Could you tell me the solution for this?
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?
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.
I would like to create a view that shows previous 3 years data as totals and additionally the last three months as months. So for the last part I could use your solution, but how adding the three years to the graph, is there a smart solution for that as well?
How do we show missing month value as 0. If we tick show missing values it breaks entire visual and ignores all filters. I try adding +0 but again ignores all filters. Thanks
In x-axis, I have fiscalmonthnumber. Can we make this in sort order. If I select June 2023. It has start with (6, 7,8...). At this moment I am getting only 1, 2, 3
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.
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
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.
But say I need to figure out the max and/or min date of the original date table (as selected) for the purpose of a measure inside the matrix. using this technique forces the context of the matrix dates on all calculations even if i use a VAR in the measure. How would I you suggest to extract the original date min/max context?
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.
This is the best solution for Rolling KPIs using a filter selection for month that I've seen. Thanks Alberto
What a Couple! Always start up looking everywhere and always end up in the Italian place. Thanks So Much.
I applied this solution using a Sparkline for a KPI Card. It's really helpful and useful for my company. Thanks Alberto!
Thank you. I love that you move through the example step-by-step to help us understand how the calculation works.
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!!!!
This is literally a life saver technique. Thank you soooo much for uploading such a great content
Damn !! Thank you very much. Thrilled to understand the reason behind every single step.
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!
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? 😊
WOW!! explained in such detail so the implementation is absolutely clear - AMAZING Alberto!
Thank you for sharing in such a detailed step by step explanation. It is brilliant and it worked perfectly.
Genius solution. This saved me so much trial and error! Thank you, Thank you, Thank you!!!!!
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!!
What a amazing explanation, I was struggling to implement similar kind of solution in my dashboard from past 3 days, thank you so much ❤
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 ;)
Once again.. Brilliant. You and your channel were my 2021's best find and I don't think anything will top that. Thank you !
Holy moly, thank you very much, you deserve more viewers, awesome content with amazing explanation!!
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! 😎
Sir u r god of SQLBI...tks for contributing towards your small learners like me..
Awesome man ... you guys are simply Ninjas of Power BI
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!!
Thank you for this. Its the only functioning solution isi have got for my data so far
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
Your solutions are so elegant. Amazing work.
Just when you think you're starting to get a grasp on DAX, this is the place that will humble you.
Wow. This solved my issue. Can I say WOW once again. You are the best.
Nice one. Always a Joy to see you breakdown DAX in simple terms
Always finding solutions here! Thank you Alberto!
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 !!
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
You save my project at work! Thank you for this, well done!
Superb.. I cracked it what I was looking for and it was for only month end dates.. Great One Alberto.. 🙏👏
Great Video , Great Explanation , love it . thank you to come online and sharing . love all your videos ...Love Dax
Grazie mille per la tua spiegazione!
As always, excellent!
Greetings from Brazil!
This is exactly what I needed in my current project. Thanks Very much.
Thank you alberto,
You literally saved me !
Keep the great work.
Awesome! Solutions to actual customer's requests.
Thank you very much Alberto, some time ago I had to solve this scenario, however, this approach is much better and more elegant
Amazing
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!
Fantastic, the explanation of filter context is great. Thanks!
Incredibal .... Amazing Alberto.. Many thanks.. It helps alot in my project.
Thank you Alberto, DAX Guru. :)
This Channel helps me a lot.
EXCELENT! I loved this explanation.
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
I love your videos! Such a good teacher! Thank you!
Thank you very much! Your explanation is perfect. Exactly what I need.
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
Very good solution. Could i use this approach to show values from the first month of the selected year to selected month? For example choosing 2024 in year slicer and October in month slicer getting the values from January to October and show values in matrix visual, monts values in their own columns.
Best teacher ever
HI....... Can anyone tell me how to show last Six month sales with discontinue month & also I want to show a month which has no sales as a null in last six month filtering?
Alberto as always you re a Dax Lord
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?
You make it look so easy!!!
Hi all,
When I try to make the deactivated relationship between the two calendar tables (Many to one), my calendar tables loses the date format, so Time Intelligence is not working. In the video, after the relationship is created, both tables keep the date format but in my case, the format is lost.
I can set manually one calendar table with Date/time column format but the second one, I am not able to do it.
Anyone knows why is this happening?
Thank you
Incredibly useful. Thank you very much!
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!
Anyone could tell me why could not we just use a measure using ALL( ) to skip the filter context which is applying by the slicer to the bar chart?
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.
Thank you so much..
Do we have video for complete Mquery/measures
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.
This worked after checking out ~5 other solutions and made sense.
Hello,
Kindly confirm if code wont work without tabular editor(14:55) and is it possible to get same result without tabular editor.
I know this is a older video, but does anyone know what formula he used for the Year Month Number? I cannot get it to sort correctly. I feel I have spent more time on the sorting issue than the entire visual...
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.
Thank you Alberto. The same solution works for the new sparkline feature!
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.
Hi I need to count 6 month data from prior month and I tried to use this Dax it don't work for me! I have two table Date and Date2.can you help me to find solution? thank you
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.
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
this is nice.but one query if i not applied any filter in month silcer i want to show the all months data on table.
Hi I want find the best sales in the selected last 12 months
Dynamically can u explain or provide the solution for that
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.
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
Another milestone! Thanks!
What if I just want to show all months of the selected Year, regardless of the Month filter in the page?
Thanks Alberto, really useful and helped a lot in one of my deliveries.
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.
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?
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.
Hi...How can we subtract the variables in calculation group..
VAR currmoth=.....
VAR lastmonth=.....
RETURN currmonth - lastmonth
The subtraction is not woorking....
Pl help.....Thanks
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?
I have the same requirement. but i need to use the calculated column instead of measure [sales Amount].
Since i am using calculated column It says table of multiple values passed. Could you tell me the solution for this?
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?
Same issue! Do we have solution for this?
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.
@rohithkothaneth
Did you find a solution for this problem?
I would like to create a view that shows previous 3 years data as totals and additionally the last three months as months. So for the last part I could use your solution, but how adding the three years to the graph, is there a smart solution for that as well?
How do we show missing month value as 0. If we tick show missing values it breaks entire visual and ignores all filters. I try adding +0 but again ignores all filters. Thanks
How can we achieve last 6 months data in clustered column chart without using tubular editor?
In x-axis, I have fiscalmonthnumber.
Can we make this in sort order. If I select June 2023. It has start with (6, 7,8...). At this moment I am getting only 1, 2, 3
Can we have multiple slicer for filtering, one being date slicer and other being name of the agent.... is it possible with this? Can you please help.
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.
very well explained video, is it possible to apply the same technique for the last x weeks
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
It is great especially the filter part! Thank you!
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.
If you use a calculated column, it would be static, the user would have no way to select the starting point
What if we connect Previous date table and Sales table, then also it gives the correct result.
Should we do that?
Thanks Alberto. But, could this be applied to a rolling measure?, Thanks
But say I need to figure out the max and/or min date of the original date table (as selected) for the purpose of a measure inside the matrix. using this technique forces the context of the matrix dates on all calculations even if i use a VAR in the measure. How would I you suggest to extract the original date min/max context?
Need this for some custom tooltips. Thank you!
What about having a disconnected date dimension and use it for filter and use the date dimension in X axis of chart.
How would i create the date table from the begining?
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.