Show last 13 Months from Slicer Selection in Power BI (DAX)

[@BIGorilla]

There is now a better solution by SQLBI. It performs better and is easier to implement: kzbin.info/www/bejne/mmm1nmqarLJpncU

[@denxx6126]

Thx... nice technique!! You can extend the logic with dynamic selection - how many months you show 1. Seperate Table > xMonths = GENERATESERIES(1, 32, 1) 2. Extand the measure by one more variable > VAR xMonths = MAX( 'xMonths'[xMonths] ) 3. Use the VAR from 2. in the other Variable > VAR MaxDate_XMonthsAgo = EOMONTH( MaxDate , xMonths ) 4. Use the field xMonths e.g. as slicer Whole DAX Measure: Sales | Last X Months = VAR MaxDate = MAX( Calendar[Date] ) VAR xMonths = MAX( 'xMonths'[xMonths] ) //VAR MaxDate_XMonthsAgo = EOMONTH( MaxDate , -13 ) VAR MaxDate_XMonthsAgo = EOMONTH( MaxDate , xMonths ) VAR Result = IF( HASONEVALUE( 'Presentation Calendar'[YYYYMM] ) && MAX( 'Presentation Calendar'[Date] ) MaxDate_XMonthsAgo , CALCULATE( [Sales] , FILTER( ALL( Calendar[Months] , Calendar[Year] , Kalender[YYYYMM] ) , Calendar[Months] = VALUES( 'Presentation Calendar'[Months] ) && Calendar[Year] = VALUES( 'Presentation Calendar'[Year] ) ) ) , BLANK() ) RETURN Result

[@DanielMazuin-q6e]

Thanks man! You solved in 9 minutes what took me two days to try to do. Saved my skin. Thank you very much!!!

[@lucasregly8734]

Thank you so much, bro! This content saved my project. I tried others DAX formules, but only yours worked! Congrats!

[@jaimerrojasrojas5924]

Thanks to share!!! I've used your solution to show before and next 12 months.

[@MrRmisu]

You're the best! I was searching for hours to make this work and you're explanation was much easier to understand.

[@mrTonYT987]

BI Gorilla = pure awesomeness. 💪 Thanks again for this tutorial.

[@hazelness7]

Yeyy it worked for me. Thank you for making amazing videos like this. You are awesome.

[@carlosmarcolino9890]

Thank you so much for sharing this solution with us. It helped me a lot with a problem in my Dashboard. Greetings from Sao Paulo, Brazil

[@teranenrique]

Thank you so much...i was looking at other examples but they were not as clear as yours

[@rjp1996]

Thanks man, great content. It helped me in my project. I needed to plot the last year's data(depending on the date picked by the user) and the year before that. So in short 1. 11 months ago to the month picked, and 2. 23 months ago to 12 months ago. I was able to show both of these measures but I don't know how to put them on top of each other.

[@paulleyland4761]

Great. I have just spent about six hours trying to do almost the exact same thing and couldn't find a similar explanation.

[@BIGorilla]

Have a look at this alternative, it may perform better: kzbin.info/www/bejne/mmm1nmqarLJpncU

[@rakeshmahakud110]

Awesome explanation

[@chpraveen5060]

Thanks its very helpful

[@thitidaphautm9468]

I would like to ask some questions. How to create the calendar "YYYYMM" and 3 columns after?

[@MoreLore_wishiwasaballer]

Great Video! I’m trying to implement this in one of my models. Can you explain why you need a presentation calendar? Could you just use the calendar that you have connected to your model?

[@BIGorilla]

Hi ben. It's explained in the video. When using the regular calender as both a filter and field on the x-axis, it would filter down to a single month. You need a separate calendar to filter 1 month, and show 13 months on the axis.

[@MoreLore_wishiwasaballer]

@@BIGorilla I just got it to work. I was putting the regular connected date YYYYMM on my graph and not the presentation date YYYYMM on the graph. Great solution! Works like a charm.

[@mohanr9522]

Hi Rick, Thanks for the great Video!! I want to show CY and LY for the last 13 months from the selected month... Can you please help how to achieve this?

[@CarlosLagos10]

Thank you so much Rick! It works for me, May you please help me to know how i can add a card with total of 13 months calculate? I mean If I have on bars chart from 012022 to 012023 how can I add a card with total in that period sum all months filtered with this formula did? In advance thanks for your help with it.

[@tubeitz]

Great video, thank you

[@BIGorilla]

Great to hear Varun!

[@SUJITMALLICK100]

Thanks for the explanation, I used the same in a Matrix visual but the issue is it given blank total col in the matrix. How to fix this could you pls explain? Thank you

[@StrategySafari]

Same issue, did you find a solution to this?

[@RozzioHernandez]

Very useful, thanks a lot. Just wonder how the user can input any number of month and even how the user could select whether to display the chart in years, months or weeks.

[@saivenkatesh566]

If I select a Day in slicer., The bar chart should show data for past 6 days including day selected in slicer.please suggest the change s to do in measure.

[@chernytan9817]

Great video! I'm new in DAX. Would like to ask why do we have to check with HASONEVALUE ('Presentation Calender'[YYYYMM]) as Year or Month of regular Calender will be filtered in the slicer.

[@neerpanwar]

yes this should be hasonevalue[calendar[year]] and hasonevalue [calendar[month]]

[@abetarabein]

This is great thank you so much.. can you pls show how to do exactly the same but showing two data point for each month (current month value and same month last year's value). so graph would be dynamic 13 months but two data points for each month. appreciated

[@hazelness7]

This is amazzinggg, been using these with my report. Is it possible to show the month still though in the x axis even if there is 0 value? so instead of returning blank it should show 0, really appreciate it if you can help me, please please😞

[@jackmacon7206]

I replicated for showing last 3 years based on selection. In a table I would like to add a running total for last three years - is there a way to do this?

[@BIGorilla]

Sure is, you would check for a different date range though

[@sureshrao174]

Sir you have not connected in your presentation date table to the data table or date table how is it working Isnt necessary though?

[@sarva0483]

Very neat explanation. I have 1 more need on this summary, if i need to add other dimensions like customer and product in a matrix visual. I tried it with multiple dimension values in filters but the performance is very slow.

[@StrategySafari]

Same issue, did you find a solution for this?

[@BIprofessional80]

What if I have too many fields in value section like total sales, total profit, total cost price, total selling price etc. Do I need to do seperate calculation for all as you did for total sales?

[@BIGorilla]

Hi Ritesh, You can use a calculation group to apply the template on all of your measures in the visual. In that way you don’t require a separate measure for each value.

[@BIprofessional80]

@@BIGorilla thanks sir

[@eswarbhimavarapu3227]

Can you help me in getting Last 4 quarters using slicer selection

[@idolsensei7]

If i have 4 data elements in the graph instead of total sales and need to show it like that, I need to perform a new measure on each of the elements right?

[@bentatlim]

Great video. i like the way, how you describe everthing step by step. But i must ask you something. What if i dont want a fix number of months (like in your video 13 months) ? What if i want to see the actual + previous month of my acutal year? That means, if i choose April as month and 2022 as year in my Slicer, i want to see : jan 2022, feb 2022, marc 2022, april 2022 in my chars. How can i do that? I try to solve it, but could not find a way :( I hope you can help me.

[@NandonPeddi]

Hi, Thank you so much for the measure, but when I add the measure to a Table/Matrix, I do not see the Totals (I mean totals row is blank) . Could you please provide a solution for that.

[@StrategySafari]

Same issue, did you find a solution for this?

[@sophyrath9299]

Can we get the same result without having Presentation Calendar?? if we only have Calendar table.

[@BIGorilla]

Unfortunately not. With a single calendar, a slicer selection would result in only showing the selection. So for example the month jan and year 2021. Your x axis would then only show that. DAX would then allow you to change the calculations and generate the sales for the last 13 months on the y axis, but the x-axis would not show the 13 months. It would show just the single month.

[@shivamahuja11orkut]

Hi.. how can i update the dax function to only show till the selected month in the selected year in the slicer?

[@chmielos2000]

Great video 🔥, I had some problems but after all is OK. I wonder why it didn't work with && in FILTER ? I had to change to AND function and it works. AND( 'Calendar'[Month] =VALUES('Presentation Calendar'[Month]), 'Calendar'[Year] = VALUES('Presentation Calendar'[Year]) )

[@sweetyranbhor8056]

Plzz help me with the scenario to get current month sale, previous month sale and last year sale

[@adriannacook313]

Thanks so much for the tutorial! It has helped me more than once! I am wondering if you could please let me how I can handle following problems: 1. On my axis, I can only add 1 field (Month Year). If I add 2 separate fields (month) and (year), the measure did not return any values. 2. How can I set up my chart to show 2 groups of years (group 2019 & group 2020) with the months sitting above the year in each group, and there is a line to split each group of year? I've been trying but my chart is showing either month only, or month-year next to each other. I could see that you drill down then sort by year month (all together), but I could only sort by either month or year. Thank you so much!!

[@idolsensei7]

Im following this so I created a Calendar sheet as well so Im connecting it to my other sheet to have a relationship but its having an error saying one of the columns must have unique values

[@topclips598]

I guess the question is can you do this for all your existing measures without having to create a new measure for each one?

[@bernardorainha7213]

It's possible to do this with a factual table with 2 dates? imagine a products factual table and you want to show last 13 months for the active products, you need to use the start date and end date of the product. can you do that?

[@leridarebello3215]

Is there any video/link to understand exclusion concept (table 1 data not in table 2) where the filters for both the tables will be dynamic. Well, What I really want to do is have a slicer where I can select a few customers (e.g: filter customers from table 2 with dynamic filter options) and then exclude them from table 1 which also has dynamic filters). So in the end you basically look for Customer IDs from both the tables, return table 1 iDs NOT IN table 2. P.S: THE FILTERS ARE DYNAMIC FOR BOTH THE TABLES. Thank you.

[@BIGorilla]

Hey Lerida. You could look into using EXCEPT. Create a table filter in CALCULATE with the difference between table1 and table2. You can achieve that with the EXCEPT function. I don't have any video on that yet, good idea though. Hope that helps!

[@leridarebello3215]

@@BIGorilla Thank you. I used except on the base tables, I will try using the table filter method and see how it works with the dynamic filters. It would be great if you could cover these topics since they are not very common on KZbin and other channels.

[@rodrigochavez7]

What would happen if you don't have data in some of those months? I suppose that will appear blank only, but how do you fill with zero columns with no data?

[@kavyaananth6007]

This is really helpful . Thanks 1. But when I tried creating the same thing in a line chart , I see only the first and last month , for ex : Feb 2021 and Feb 2020 I cannot see the months between these 2. If I have multiple measures done at 13 period like total profit , total orders , then is there a way to do it instead of creating a measure for each like this ? 3. Is it possible to have this directly in a single slicer as yyyymm instead of two slicers ? Thank you

[@BIGorilla]

Hi Avya, I'm glad to hear this helps, thanks! Regarding your questions: 1. Please double check the fields you put on the axis. From what you write, it sounds to me as if the month field is still from the regular calendar, instead of the presentation calendar. 2. If you use calculation groups, you could use this to apply the 'template' formula to each of the measures on your page. So then you don't have to make seperate measures for each. However, without you will have to apply it to each measure seperately. 3. Sure, you can use a YYYYMM slicer, no problem :) Enjoy ! Rick

[@shalom1070]

Hey - This is a great video but unfortunately not working for me. The min function is complaining saying I can only use it to reference a column and I have a date column in my date table. Not sure why I get this error. Any advice?

[@BIGorilla]

Hey Ed. I could explain this, but a better video is the one released by sqlbi : kzbin.info/www/bejne/mmm1nmqarLJpncU

[@shalom1070]

@Gorilla BI - It worked for me! I just needed to get back at it with fresh eyes! Thanks for the wonderful video!!!!

[@jtobar]

Hey BI Gorilla, I have de following issue with a similar graph that calculates the comulative % of close tickets (requierements) since 2019 to the date. When put into a graph obviosly is huge due to the months... when i use the relative date filter to show the las 6 month... it alfo filters the data source so the comulative % is not the same as the running total... it will only take the data filtered based in the condition use in relative date.... I only want to modify the graph visualy to just shortened with out altering the data use to calculate the running %... is this posible? I search all over and couldnt find a solution... Help please!

[@angelav7999]

Hello Gorila, amazing content but I have a problem: my formula doesn’t display the result. Could you please tell if can be a problem in my column YYYYdd.? My column YYYYdd is text type but I charge to date it’s an error. Sorry Go, I am entry level and hope you’ll find some time for answer

[@luiztargino2153]

Excellent video. I have been trying a lot of solutions, but this is the only one working for me. Now I have an issue. In my model, the filter only works when I apply a column to the visual (matrix) on date format (mm/yyyy) as rows. In your model, you are able to put both Month and Year from the calendar table on Text format. How can I do that? I have been trying to apply this same model to a visual showing a matrix only year-by-year and the user can select a month/year single slicer. But when I put month and year on the visual, it doesn't work (it is showing a blank matrix). It only works when I put a date format column on the visual, but I need to group by years. Do you have any suggestions? Thank you.

[@siddheshamrutkar8684]

Can we use this in matrix visual? I need to find the last 13 month end date once I use any year or date in the matrix visual and it should go back to exact last 13 month end dates..

[@StrategySafari]

Same issue, did you find a solution for this?

[@parthparikh9674]

Excellent video! I am trying to see LY sales along with TY sales for the last 13 months on the same graph. And hence calculate the growth %. But PBI is not giving correct results. For example => 1. showing sales from 2019-12 to 2020-12 for this year as a line chart 2. showing LY sales (i.e. sales from 2018-12 to 2019-12) on the same axis and in the same chart with TY sales from 2019-12 to 2020-12 So, just having x-axis values from 2019-12 to 2020-12 and showing TY and LY sales there. Right now it is creating a separate timeline for LY sales from 2018-12 to 2019-12 in the same chart. Any way to move this LY data over to the x-axis values from 2019-12 to 2020-12?

[@BIGorilla]

Hey Parth. You can also include the last year numbers in the graph. Do it by making another measure with the same logic. So including the check for the presentation calender. The only change is the referenced measure in the statement. Does that help?

[@parthparikh9674]

@@BIGorilla I am creating another measure for LY numbers. I am not taking the exact formula that you have here but using a Month Index Column (I think you have that in one of your videos) and joining the sales table with the date table using that Month Index Column. So, basically, "Maxdate = MAX(Month Index Column)" and "Maxdate_13Monthsago = MAX(Month Index Column) - 13" and for LY sales I am using "Maxdate = MAX(Month Index Column) - 12" and "Maxdate_13Monthsago = MAX(Month Index Column) - 25" Doing this, I am getting correct values for LY but in its original timeline. Instead, I need LY values in the TY timeline. Any way to tell PBI what you are doing is right but just show it with respect to the TY axis?' I have also tried doing 'Calendar'[Year] = VALUES ('Presentation Calendar'[Year]) - 1 but this is giving me blanks throughtout

[@BIGorilla]

I believe you can change the measure as provided in the video. Just add an argument that says SAMEPERIODLASTYEAR( calendar[date] ). But leave all the other arguments 😁 If that doesnt work, please first create a separate measure. In my example I have a [sales amount] You can make a [Sales amount PY] which is CALCULATE( [Sales Amount], SAMEPERIODLASTYEAR( Calendar[date])) Then create the measure as I made in the video, and replace [sales amount] with [sales amount PY]. I believe it will work :)

[@dmalq]

Gracias, busque mucho esta solución

[@BIGorilla]

De nada :)!

[@ponnamallikarjuna]

Is it possible in direct query

[@BIGorilla]

I'm not sure.

[@ponnamallikarjuna]

Iam tried in direct query but it's showing error, is there any alternative to show 18 months data, 6 months forest data and 12 months actual data by selecting slicer single date selection

[@Tsuxoy]

And what if I just want to show all months of the current Year, regardless of the Month filter in the page?

[@BIGorilla]

You can disconnect the month slicers and filter your data with only a year slicer.

[@Tsuxoy]

@@BIGorilla Thank you!

[@zuhebkhan6973]

Hi Sir..I tried to implement the same way what you showed in the video but...it does not work....I can share my PBI work book and data set for the same if you provide me you email id I will share..so you can guide me what wrong in I have done. But I'm sure I have used same dax what you have prepared in video. It will a great help Sir. Thanks in advance

[@BIGorilla]

Hi, please have a good look which fields you put in the axis. It may very well be that you switched around the presentation calendar with the regular one. Hope that helps! Rick

[@zenny2027]

Can you pls share the pbix file

[@hamzamottali3567]

Hello , please I have a question . i calculated a value by measure and I want to display automatically this value in the next month, how can i do that ? i tried nextmonth fucntion but didn't work with me, would you help me please ?

[@namangarg7023]

Why didn't u used Sir Alberto approach for same this one is confusing and so many recursive logical statements making algorithm more complex

[@BIGorilla]

The latest pinned comment (of 2 years ago) also refers to alberto's method. However, there are still niche scenarios where that doesn' twork

[@nyamjargalsh12]

Thanks a lot

[@mnowako]

Awesome!

[@kummithavenkatareddy2302]

I have data up to NOV 2021 when i am WORKING in 2/Feb/2022. I have created calendar using Min( Fact_Table[Billing-Date]), MAX(Fact_Table[Fisca-lDate]). Now in my slicer i want to show latest Month NOVEMBER dynamically (by NOT selecting manually) & Previous Months. If I get any data like in Dec2021 in the back end, in the slicer i want to see Dec Month whenever i refresh the file. We are using USA fiscal policy. Use DAX ONLY FISCALDATE FOR ALL THE DATES IN NOVEMBER IS 6/1/2021 ( COMES UNDERS FY22 ACCORDING TO US FISCAL POLICY. I WANT ALSO SHOW PREVIOUS FISCAL MONTHS IN SLICER. bIILING DATE IS Nov/1/2021 fiscal date is Nov/1/2022

[@rajant3546]

For me this is not work I don't know why

[@royanwilliams50]

This is great! I'm trying to replicate what you've done for one of my clients but with a bit of a twist. They're asking for 13 weeks rather than months and to make it even more complex, they work on a 445 calendar. Any clue on how to tackle this?

[@BIGorilla]

Exciting stuff Royan! I've worked quite a few with the Fiscal calendars too. Since the regular week functions probably don't fit your needs, try adding an index column to your fiscal calendar. For each increment in the week, make sure the index also increase. So week one is index 1, week two index 2, but notice that week 100 = index 100, and not index 48. You can use the construct I used in the below video with method 3. Just replace it by the correct WeekIndex column. kzbin.info/www/bejne/aaTJi2yre6mGgpY Hope that helps and keep crushing it! Rick

[@royanwilliams50]

@@BIGorilla Thanks. I'll take a look and let you know how I got on

[@primaconsulting9919]

👍