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

แชร์
ฝัง
  • เผยแพร่เมื่อ 30 ม.ค. 2025

ความคิดเห็น • 96

  • @BIGorilla
    @BIGorilla  3 ปีที่แล้ว +16

    There is now a better solution by SQLBI. It performs better and is easier to implement:
    th-cam.com/video/d8Rm7dwM6gc/w-d-xo.html

  • @denxx6126
    @denxx6126 3 หลายเดือนก่อน +1

    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

  • @lucasregly8734
    @lucasregly8734 2 ปีที่แล้ว +1

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

  • @DanielMazuin-q6e
    @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!!!

  • @jairojas100
    @jairojas100 2 ปีที่แล้ว

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

  • @hazelness7
    @hazelness7 3 ปีที่แล้ว +1

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

  • @carlosmarcolino9890
    @carlosmarcolino9890 7 หลายเดือนก่อน

    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

  • @MrRmisu
    @MrRmisu 2 ปีที่แล้ว

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

  • @mrTonYT987
    @mrTonYT987 10 หลายเดือนก่อน

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

  • @teranenrique
    @teranenrique 10 หลายเดือนก่อน

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

  • @rjp1996
    @rjp1996 3 ปีที่แล้ว +2

    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
    @paulleyland4761 3 ปีที่แล้ว +2

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

    • @BIGorilla
      @BIGorilla  ปีที่แล้ว

      Have a look at this alternative, it may perform better: th-cam.com/video/d8Rm7dwM6gc/w-d-xo.html

  • @rakeshmahakud110
    @rakeshmahakud110 3 ปีที่แล้ว

    Awesome explanation

  • @MoreLore_wishiwasaballer
    @MoreLore_wishiwasaballer 4 ปีที่แล้ว +1

    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
      @BIGorilla  4 ปีที่แล้ว +1

      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
      @MoreLore_wishiwasaballer 4 ปีที่แล้ว

      @@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
    @mohanr9522 3 ปีที่แล้ว

    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?

  • @chpraveen5060
    @chpraveen5060 3 ปีที่แล้ว

    Thanks its very helpful

  • @RozzioHernandez
    @RozzioHernandez 3 ปีที่แล้ว

    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.

  • @soumodeepneogi9924
    @soumodeepneogi9924 12 วันที่ผ่านมา

    Nice technic! But how do I get the % of variance between months?

  • @thitidaphautm9468
    @thitidaphautm9468 2 ปีที่แล้ว

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

  • @chernytan9817
    @chernytan9817 2 ปีที่แล้ว

    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
      @neerpanwar 2 ปีที่แล้ว

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

  • @CarlosLagos10
    @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.

  • @SUJITMALLICK100
    @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
      @StrategySafari 11 หลายเดือนก่อน

      Same issue, did you find a solution to this?

  • @tubeitz
    @tubeitz 4 ปีที่แล้ว +1

    Great video, thank you

    • @BIGorilla
      @BIGorilla  3 ปีที่แล้ว

      Great to hear Varun!

  • @sarva0483
    @sarva0483 3 ปีที่แล้ว

    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
      @StrategySafari 11 หลายเดือนก่อน

      Same issue, did you find a solution for this?

  • @hazelness7
    @hazelness7 3 ปีที่แล้ว

    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😞

  • @saivenkatesh566
    @saivenkatesh566 2 ปีที่แล้ว

    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.

  • @topclips598
    @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?

  • @jackmacon7206
    @jackmacon7206 3 ปีที่แล้ว +1

    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
      @BIGorilla  3 ปีที่แล้ว

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

  • @abetarabein
    @abetarabein 3 ปีที่แล้ว

    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

  • @idolsensei7
    @idolsensei7 2 ปีที่แล้ว

    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?

  • @NandonPeddi
    @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
      @StrategySafari 11 หลายเดือนก่อน

      Same issue, did you find a solution for this?

  • @bentatlim
    @bentatlim 2 ปีที่แล้ว

    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.

  • @adriannacook313
    @adriannacook313 3 ปีที่แล้ว

    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!!

  • @BIprofessional80
    @BIprofessional80 2 ปีที่แล้ว

    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
      @BIGorilla  2 ปีที่แล้ว +1

      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
      @BIprofessional80 2 ปีที่แล้ว

      @@BIGorilla thanks sir

  • @sureshrao174
    @sureshrao174 2 ปีที่แล้ว

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

  • @sophyrath9299
    @sophyrath9299 3 ปีที่แล้ว

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

    • @BIGorilla
      @BIGorilla  3 ปีที่แล้ว

      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.

  • @eswarbhimavarapu3227
    @eswarbhimavarapu3227 3 ปีที่แล้ว

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

  • @chmielos2000
    @chmielos2000 3 ปีที่แล้ว

    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])
    )

  • @idolsensei7
    @idolsensei7 2 ปีที่แล้ว

    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

  • @mnowako
    @mnowako 3 ปีที่แล้ว

    Awesome!

  • @sweetyranbhor8056
    @sweetyranbhor8056 ปีที่แล้ว

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

  • @shivamahuja11orkut
    @shivamahuja11orkut ปีที่แล้ว

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

  • @kavyaananth6007
    @kavyaananth6007 3 ปีที่แล้ว

    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
      @BIGorilla  3 ปีที่แล้ว

      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

  • @leridarebello3215
    @leridarebello3215 4 ปีที่แล้ว

    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
      @BIGorilla  4 ปีที่แล้ว +1

      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
      @leridarebello3215 4 ปีที่แล้ว +1

      @@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 TH-cam and other channels.

  • @rodrigochavez7
    @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?

  • @bernardorainha7213
    @bernardorainha7213 2 ปีที่แล้ว

    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?

  • @luiztargino2153
    @luiztargino2153 2 ปีที่แล้ว

    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.

  • @parthparikh9674
    @parthparikh9674 3 ปีที่แล้ว

    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
      @BIGorilla  3 ปีที่แล้ว

      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
      @parthparikh9674 3 ปีที่แล้ว +1

      @@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
      @BIGorilla  3 ปีที่แล้ว +1

      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 :)

  • @siddheshamrutkar8684
    @siddheshamrutkar8684 3 ปีที่แล้ว

    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
      @StrategySafari 11 หลายเดือนก่อน

      Same issue, did you find a solution for this?

  • @shalom1070
    @shalom1070 3 ปีที่แล้ว

    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
      @BIGorilla  3 ปีที่แล้ว

      Hey Ed. I could explain this, but a better video is the one released by sqlbi : th-cam.com/video/d8Rm7dwM6gc/w-d-xo.html

    • @shalom1070
      @shalom1070 3 ปีที่แล้ว

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

  • @jtobar
    @jtobar 2 ปีที่แล้ว

    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!

  • @nyamjargalsh12
    @nyamjargalsh12 ปีที่แล้ว

    Thanks a lot

  • @angelav7999
    @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

  • @Tsuxoy
    @Tsuxoy 9 หลายเดือนก่อน

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

    • @BIGorilla
      @BIGorilla  9 หลายเดือนก่อน +1

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

    • @Tsuxoy
      @Tsuxoy 9 หลายเดือนก่อน

      @@BIGorilla Thank you!

  • @hamzamottali3567
    @hamzamottali3567 3 ปีที่แล้ว

    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 ?

  • @ponnamallikarjuna
    @ponnamallikarjuna 3 ปีที่แล้ว

    Is it possible in direct query

    • @BIGorilla
      @BIGorilla  3 ปีที่แล้ว +1

      I'm not sure.

    • @ponnamallikarjuna
      @ponnamallikarjuna 3 ปีที่แล้ว

      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

  • @namangarg7023
    @namangarg7023 5 หลายเดือนก่อน

    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
      @BIGorilla  5 หลายเดือนก่อน

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

  • @zuhebkhan6973
    @zuhebkhan6973 3 ปีที่แล้ว +1

    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
      @BIGorilla  3 ปีที่แล้ว

      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

  • @dmalq
    @dmalq 3 ปีที่แล้ว

    Gracias, busque mucho esta solución

  • @zenny2027
    @zenny2027 ปีที่แล้ว

    Can you pls share the pbix file

  • @primaconsulting9919
    @primaconsulting9919 3 ปีที่แล้ว +1

    👍

  • @royanwilliams50
    @royanwilliams50 3 ปีที่แล้ว

    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
      @BIGorilla  3 ปีที่แล้ว +1

      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.
      th-cam.com/video/3sfZ9uFDSL4/w-d-xo.html
      Hope that helps and keep crushing it!
      Rick

    • @royanwilliams50
      @royanwilliams50 3 ปีที่แล้ว

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

  • @rajant3546
    @rajant3546 2 ปีที่แล้ว

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

  • @kummithavenkatareddy2302
    @kummithavenkatareddy2302 3 ปีที่แล้ว

    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