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!
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.
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
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 ?
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.
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
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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-wLPvYq82TN4.html
@@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
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.
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 !
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?
@@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
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?
@@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
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