Show last 6 months based on user single slicer selection
Вставка
- Опубліковано 21 лип 2024
- How to use calculation groups to show the behavior of any measure in the last 6 months (or any other number of months), starting from a single date selection with a slicer.
Article and download: sql.bi/704248?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... - Наука та технологія
Thank you for sharing in such a detailed step by step explanation. It is brilliant and it worked perfectly.
Thank you. I love that you move through the example step-by-step to help us understand how the calculation works.
WOW!! explained in such detail so the implementation is absolutely clear - AMAZING Alberto!
Once again.. Brilliant. You and your channel were my 2021's best find and I don't think anything will top that. Thank you !
What a Couple! Always start up looking everywhere and always end up in the Italian place. Thanks So Much.
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!!
This is the best solution for Rolling KPIs using a filter selection for month that I've seen. Thanks Alberto
I applied this solution using a Sparkline for a KPI Card. It's really helpful and useful for my company. Thanks Alberto!
Nice one. Always a Joy to see you breakdown DAX in simple terms
Damn !! Thank you very much. Thrilled to understand the reason behind every single step.
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
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
Your solutions are so elegant. Amazing work.
Holy moly, thank you very much, you deserve more viewers, awesome content with amazing explanation!!
Superb.. I cracked it what I was looking for and it was for only month end dates.. Great One Alberto.. 🙏👏
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? 😊
Great Video , Great Explanation , love it . thank you to come online and sharing . love all your videos ...Love Dax
Genius solution. This saved me so much trial and error! Thank you, Thank you, Thank you!!!!!
You save my project at work! Thank you for this, well done!
Wow. This solved my issue. Can I say WOW once again. You are the best.
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 ;)
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!!
Always finding solutions here! Thank you Alberto!
This is exactly what I needed in my current project. Thanks Very much.
Fantastic, the explanation of filter context is great. Thanks!
Awesome man ... you guys are simply Ninjas of Power BI
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 !!
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!
Thank you Alberto, DAX Guru. :)
This Channel helps me a lot.
Thank you very much! Your explanation is perfect. Exactly what I need.
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! 😎
Thank you alberto,
You literally saved me !
Keep the great work.
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
Awesome! Solutions to actual customer's requests.
Grazie mille per la tua spiegazione!
As always, excellent!
Greetings from Brazil!
Incredibal .... Amazing Alberto.. Many thanks.. It helps alot in my project.
I love your videos! Such a good teacher! Thank you!
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
Incredibly useful. Thank you very much!
Thanks Alberto, really useful and helped a lot in one of my deliveries.
Thank you Alberto. The same solution works for the new sparkline feature!
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?
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.
It is great especially the filter part! Thank you!
You make it look so easy!!!
Thanks for the video!
When we click on the month axis, it will cross-filter other visuals on the same page. The cross-filtered visuals display incorrect result because the filter context from calculation group will be applied. Is there anyway to prevent this inside the code instead of manually disabling cross filter for each visual ?
Thank you very much Alberto, some time ago I had to solve this scenario, however, this approach is much better and more elegant
Amazing
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!
Thank you!!! This is exactly what I was looking for =)
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
Need this for some custom tooltips. Thank you!
Alberto as always you re a Dax Lord
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.
Another milestone! Thanks!
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.
This worked after checking out ~5 other solutions and made sense.
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! You are truly a life saver :)
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
This is great, thank you for your help.
Hi, great video i've loved it so useful, i have a problem, eg : when i try to show the last 6months for march2020 it takes in consideration the months only so it shows feb,jan,dec,... for 2020 all of them.so i want dec,nov for 2019 how to solve this ?
Thanks a lot ,but I have a question,can we create a virtual table to replace the table created for the previous date?
You’re amazing! Thank you.
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!!
Hello Alberto. I have same use case as you're showing, but with one difference. I need to compare last n months based on user date selection with last n months same period, previous year. That means, that I need to have mm instead of yyyy-mm on the axis. How would you then sort months (e.g.: selected date february = 2 ; 1, 12, 11, ...). I did it with DAX measure, which is fast, but the code is quite long and not elegant at all. Thanks!
Hi Alberto! I tried this method and worked perfectly but now i'm curious: How would I adapt the code to see only the months in the current year select. Meaning if I choose April 2021 I only get to see April, March, Feb and Jan 2021.
Thank you
Thank you so much..
Do we have video for complete Mquery/measures
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
very well explained video, is it possible to apply the same technique for the last x weeks
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.
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?
Best teacher ever
Great Video, Thanks.
Q: When I select FY 2021 & Jan in the slicer, my line chart needs to show April till Jan data. same as when I select Dec, it needs to show April till dec only, How can I achieve this
Very clever! Thanks for sharing.
I have 4 quarters and need to showcase qoq growth % only for current quarter in matrix table can I use Quarter instead of month in dates in period. Plz respond here
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.
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 great video, I modified certain things and it works like a charm, however whenever I'm creating the relationship I lose the date hierarchy in my original calendar table. What am I doing wrong?
What about having a disconnected date dimension and use it for filter and use the date dimension in X axis of 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
Hi, How to sort the filter "Calendar Year Month" as presented in the video? Mine is showing Jan-18, Jan-19, Jan-20 in the list.
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?
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.
what is the difference between activate or not activate the relationship on date and prevdate?
Thanks Alberto. But, could this be applied to a rolling measure?, Thanks
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.
Is there a way to add running totals? for example - I am showing the monthly amounts for last two years based on a slicer selection. I would like to add additional column to the table which summarizes the totals for each of the last two years and LTM?
Really Helpful Sir.
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.
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
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?
Hi, can this work on Filter pane instead of slicer? I select an identifier on Filters pane (identifier is not just date, but combination of Date+Name, however the table also contains the date as separate column). I would like for chart to show last 4 quarters in bar chart and table visual.
Thank you in advance!
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
Hi Sir,
Question, what if I have another visual a card visual to be exact which needs to show the figure on that month only. I know that for this tutorial it will show the last 6 months in graphs if we filter a month but If I have one slicer for date only, can that be used to show the figure on the card visual as well cos I will be needing to show the last 13 months in a graph visual? But I need to show the figures on that particular month on a card visual
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!
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.
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?
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
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.
Sir, mine does not work after adding on keepfilters function, may u advice? Tq
This is great presentation