Custom Date Period Selections in Power BI

[@disconoodles-zc6po]

I love this video. This is the level of finesse I've been looking for in a Power BI user

[@carolineashby2001]

I rarely ever comment on a KZbin video- but this one quite literally saved me. After spending DAYS researching a way to use overlapping dates in a slicer with both Last 12 Months & Previous Year, this video gave me a solution. THANK YOU!

[@alexandregol4053]

Perfect. Thanks, been trying to do this for more than 4 days now.

[@lmeza1983]

as a newcomer to power Bi this is gold, thank you!

[@emirtuncer]

Disabling the slicer impressed me! Thanks for the video :)

[@BIElite]

Thanks for watching!

[@deejohn064]

Thanks! This little trick is really helpful !! Any idea if it is possible to just default the Date Slider to a MIN Date, like Today()-45, so when the report opens it just shows the last 45 days of data and the user can then make any changes they want

[@isjajo]

Thaank you so much for highlighting that the relationship direction has to be BOTH!!! I could not figure out why my slicer did not change the data, because I had set the relationship. But I only set it to single-direction, hence the error. Anyway thanks so much this was very helpful.

[@rammaurya-m9f]

Thank you for providing this simple and perfect DAX, hope will get more helpful videos.

[@WiardingModule]

Thanks for this, very handy. One issue I am coming across even after setting the interactions is that if you set a custom slicer date period and then select a Type it will only show the data that was in the previous custom select period and not from the entire date range. It somehow needs to reset the slicer to all data before setting the type again.

[@sdiddy2010]

Thank you, the only issue I'm facing is that my table has dates with times, and the filter is looking for 12:00. Is there anyway to exclude the hours on the filters?

[@squantrill]

Not taking anything away from the content creator, but for anyone who has identified the issue of the switching back from the custom calendar to preset dates and their model breaking, along with the performance issues of the measure that disables the slider, also the slider sticking when switching to custom. Don't use the custom measure to disable the slider, use Bookmarks (with the Data enabled) to switch between the Preset calendar and Date slider to show and hide each option. The Bookmarks will reset the slider when switching back to Preset dates for example, solving the issue. Create a sexy button that switches between the bookmarks

[@schlammi123]

Umsatz Netto = var umsatz = Sum(FactOrderLine[price_totalprice]) RETURN IF([Disable Date Slicer] = 0, umsatz, CALCULATE(sum(FactOrderLine[price_totalprice]),Filter(ALL(DimDate[Date]),DimDate[Date]>Date(2000,01,01))) ) this how I solved the problem. you have to do this for every measure you use

[@sdiddy2010]

I'm stuck, can you explain this more?

[@angelarojas8509]

Thanks! This is really helpful. I'm having trouble with something. I need to put a period slicer with the following options: 'Current quarter', 'Last Month', 'Last Quarter', and 'Last Year'. However, I don't know how to keep both types of periods 'Current quarter' and 'Last Month'. How can I add the type 'Current Quarter' for the current year, without losing the dates with the type "Last Month"?

[@bentatlim]

Thank you for this amazing Video !!! But i have a problem. I did it like your describe, BUT when i choose "Custom" and set a date ( for Example 01.01.2024 - 03.01.2024) and choose after that "MTD" or "Last 10 Days", the Custom Data slicer keep the date (01.01.24-03.01.2024) . It does not reset , although i coose "MTD" or the others. I have to click on the "Delete" Button, to delete the date in my custom selection. Do you know, how i can use the slicer, without delete the custom selection every time? Thank you very much in advance!

[@RowellLagman-ht1qg]

This appears to be not working anymore. When disabled, the date slicer kept the last dates in between and still slicing even the other periods are selected.

[@chandanamb9061]

Hey did you find any solution to it?

[@DhiegoRodrigues]

@@chandanamb9061 Metabase

[@abhishekstatus_7]

Thats really awesome !! Going to implement in the report right now ! Thanks a ton for sharing this !!

[@BIElite]

Glad to hear, Abhishek! Hope it turns out well.

[@ibmyyc]

Great video! Liked and subscribed. Only thing I didn't like was the cursor blurs out the main area! For people who don't wanna use separately created date columns and link them to use this feature, I created field parameters and then used the 'Fields' column's value as text in the 'Selectedvalue' code shown in the video. That gave me the same functionality.

[@olu3468]

My jaw is still on the floor lol. Just talked to a client about this today. Thanks a bunch.

[@BIElite]

Haha I love when the timing works out like that 😊

[@LaurenPlaine]

Great video, I downloaded the pbix too and set this up successfully in my data model. Thank you! I engage with this content.

[@PallaviChauhan91]

Thank you so much for this trick. Power Bi Time visuals are really somewhat confusing to setup. This will be helpful.

[@sbsumit1]

Awesome.. This is very straight forward and help to achieve more interactive dashboard.

[@BIElite]

Glad to hear that, Sumit 😊

[@boqunyin2797]

Awesome! Thank you so much for the video! One question for the "Last Month", I think it should be PREVIOUSMONTH(DATESMTD('Date'[Date]))..., please correct me if I am wrong.

[@duongtrinh2487]

It is. I tried and it works for last month.

[@PallaviChauhan91]

Yes, it doesn't work with DATESYTD, only with DATESMTD. Similar with Last QtR [DATESQTD]

[@aarushisinha8814]

Hi, it's an amazing video I have 1 question, I'm using column and line graph and a slicer to display the dates, same as used in video But as I'm moving my date slicer the graph is not reflecting the changes Please suggest how I can fix that. Thanks

[@tharinjunjamsang4802]

Thank you so much for the video. If I want to show last 13 months, could you please explain how to do that?

[@shubhamsanas5242]

Hello, Thankyou for an eye opening Tuto.. Have facing errors the rest is working but data isn't reflecting after selecting the types...? Tips please ASAP.

[@NhungNguyen-ww3zo]

Thanks for your sharing. Btw can ad share about how to create text "Custom selection disabled" on the top of the time-range slicer, please?

[@sushreesangeetajena7646]

Great Video, sir. What would be the changes, if i want to add 'last 10 days' option to this. Please advise Thank you

[@marklund7498]

Great video, thanks Parker. Really like the measure to disable, learned something new there :)

[@BIElite]

Awesome to hear, Mark!

[@ChalapathiCV-q5y]

Great video!! Can you please help me on how to get the "Previous Week" (last week) default slicer selection.

[@dobrodziej]

Hi Parker, great ideat as usual! But have you tested it against playing around with custom date periods via a direct date slicer and then with designed period options on your new slicer? It seems to work until you won't limit dates with the direct calendar table date slicer, so until more or less all dates stay in scope. If you start, let's say from 15Nov with your custom selection then the alternative selection for Last Qtr won't override this Nov15th and will give you dates only since the mid of Nov not the the 1stOct. I don't know if there might be solution for this case... EDIT: I missed the very first question of Hayven and your reply : ) You mean the reset button as a bookmark for the particular visual, I guess. I wish it could be triggered with this alternative selection...

[@mmace31]

The user would need to use the 'clear' button on the custom date slicer, before switching back to the new slicer. Kind of a bummer...but I suppose something that those who insist on custom dates, could learn to do. Maybe...

[@montalbaconsulting5871]

@@mmace31 I've worked around this using a Toggle Switch which flips between Custom and Periods using Bookmarks. The Bookmark for the Periods options is set up with the (hidden) Custom Range cleared of any selections (and Date Slicer Filter applied). Similarly, the Bookmark for Custom has the Periods cleared of any selection (and Date Slicer Filter on Custom range cleared). Custom is removed from the Date Periods table as it is selected via the toggle not the list of Types.

[@animuserectus]

When I create the date table min/max based on the sales dates on my data sources, it gives me a time frame starting in the 1900's!

[@gilbertoacosta4401]

Nice video!, i would like to know how also add "Last 2 week" and "Last x Days" in the same column "Type" . Thks

[@OlaoluwaOlaniyi]

DID YOU GET A SOLUTION FOR THIS PLEASE

[@stevebrailsford5742]

Hi Parker How does this work with direct Query (where you dont want hybrid model) can the table be a query, if so how could i do that ? Thanks

[@NhungNguyen-ww3zo]

thanks for sharing. BTW can ad share how to create text "Choose a custom date range" in the top of the time range slicer?

[@conniemaldonado4884]

That was AWESOME! Wow. I have done something similar but not near as elegant and inclusive. THANK YOU!

[@AaronPaul_google]

Just what I was looking for. Fantastic.

[@rashidkhan8161]

great video, how can we make another slicer which could control daily, monthly & quarterly

[@mayanksapra9680]

Thanks for Video, Can this be done dynamically (using slicer date value , instead of today() in calculated table).

[@manenoalex5664]

Thanks for sharing this. My Slicer is working okay only that it leaves out some months after filtering. E.g. If i select YTD, it leaves out Apr, May and July. It has the same issue for all other selections. What could be the problem? Kindly assist.

[@poojachoudhury2227]

Thanks for the video.. this is what I wanted to learn exactly ... I also subscribed your channel. Hoping to learn more from your videos.. Keep up the good work!!

[@joebanks5420]

Exactly what I needed... Thanks... Question... I can't seem to get PreviousXXXXX and DatesBetween to work together in the same Union statement... For Example: When I have ADDCOLUMNS(PREVIOUSYEAR('Date'[Date]), "Type", "Previous Year", "Order", 3), ADDCOLUMNS(DATESBETWEEN('Date'[Date], Now(), Now()), "Type", "Today", "Order", 4). "Previous Year" does not show up in my Date Period slicer. Any idea why? Thanks

[@reagankoch4430]

did you ever figure this out. I am having the same issue

[@preranaamatya7441]

The last month and last quarter is not working correctly for me. It is showing last years last month (dec 2019) data while my end date is oct 2020. Any idea what might be wrong? The filter for date range is fine which is from Apr 2019 to Oct 2020. ADDCOLUMNS(PREVIOUSMONTH(DATESYTD(DimDate[Date])),"Type","Last Month", "Order", 4)

[@omarasim-q4j]

This was perfect. Thank you so much for this. Can someone help me and tell me how I can do the past 6 months too? I am pretty new to power BI

[@Bartman2006]

I found this really helpful and adds a level of flexibility to the user. I've managed to implement this into of my reports but wondered how you did the dynamic titles based on the selection? I'm guessing its using the SELECTEDVALUE function, but I couldn't figure it out :(

[@BIElite]

Yes, you’re exactly right! The dynamic titles add a little bit of touch

[@leegarrett1908]

Great video except there's no last week option how would you add this? Thanks

[@squantrill]

using sql when importing a calendar i would add an extra column with values of last week and this week for dates that fall within. duplicate these tables off and detach and filter them by last week and this week, then use the calendar(min(mycalendarlastweek), max(mycalendarlastweek)) string to pull these dates dynamically through

[@conradroots6728]

This is a thing of beauty!

[@omkarpatil4362]

Thanks for this wonderful Explanation, What if, i selected custom, then i change start and end date, and again i selected last Qtr, will report get filtered ? scenario, date range is from jan 2023 to jan 2024, i selected Custom, and changed Start Date to March 2023 and end date to April 2023, now if i select Last Qtr as option , i.e. Oct2023, Nov 2023 and Dec 2023, will report gets filtered ? or it will show blank in report ?

[@vadim534]

When I play with the custom slicer and choose the specific period of time, it does not reset filter if i switch to the All Time for example (shows the same datat as custom). In my case it works only if I reset custom to the whole period of time. Is it my problem or the limitation of this approach? Thanks

[@AlenaFaulova]

Hello there, tnkx for great tutorial. What if there are several dates columns? Is there anyone to advice how to create relation between 3 different dates in 2 data sets for one calendar filter? 1. data set contain ID, Name, Interview planned, Onboarding. Set 2. contains ID and Leaving Date. I want to use one Date filter and see in any visual if I choose Jan 2024 how many people where planned to interviewed, how many onboarded a and how many left. Thanks for advice.

[@bonniehsueh6848]

This trick is awesome! I need to do the same thing but instead of dates relative to today's date, I need it relative based on a date the user selects. I feel I have it conceptually but when I try to recreate what you did, I lose the date periods as soon as I join the Date Period table to the Date table on date and switch to both directions. Any ideas on what I may be doing wrong and how to achieve what I'm looking for is greatly appreciated.

[@gorkemgorkem8894]

looks like so practical, thank you !

[@BIElite]

Glad to hear it!

[@Iasdsfdsdaerbrt]

Great feature! thanks! However if we need to use this custom selector with previous dates measure, it returns blank. like PY_Sales = CALCULATE ( [Sales], SAMEPERIODLASTYEAR(Date[date])). Could someone help with this please?

[@JosefineNord]

Hi Ulad C, i am having the same problem. Did you find any good solution for this? :)

[@Iasdsfdsdaerbrt]

@@JosefineNord Yep) Try to use Crossfilter function in order to deactivate the relation for the "Previous" measures .

[@bestvca]

@@Iasdsfdsdaerbrt Can you explain how?

[@douglasbrito2921]

Amazing trick, very helpful

[@user-uu8mi8no2m]

How did you get dynamic heading change on custom date slicer?

[@ricardoabella867]

Hi Parker, I appreciate it very much, it is so useful, and I have it in my Power Bi Master file

[@sbmrdeo]

You are amazing, Parker!

[@BIElite]

Thanks Ramesh!!

[@iwona2335]

fantastic job , thank you for making our lives easier :)

[@slavikiln]

Problem is that if you move max date in custom slicer and then use relative slicers it will break all calculations and visuals. It does not actually disable the 'custom' filter itself - it just makes it unresponsive. What I mean is that if today is 31 December 2020, I move custom slicer to show data TILL 1st July 2020 and then select MTD relative slicer then all visuals will be empty cause both date filters will be applied at the same time. Moreover, you in order to fix it you will first need to click on custom, move max date and only then select the relative slicer.

[@lqqqqqqqqqqqqqqqqlq]

Thanks Parker! One additional question - Can this custom period selection be reworked in a way that allows multiple selections of more than one period at the same time? Imagine you have the following scenario: slicer with multiple KPIs (TotalCost, TotalSales) and your slicer with dynamic date selection. Can we rework this in a way that shows 2 columns in matrix visual e.g. Total Sales for YTD and Total Sales LYTD when the user selects two periods from the dynamic date selection.

[@hmntkmrTO]

Did you find the solution for PY, SAMEPERIODLASTYEAR?

[@noneB974]

You need calculation groups for it using tabulareditor

[@OlaoluwaOlaniyi]

Amazing video.....Parker. How can i add last 1 week, Last 2 weeks, Last 1 month and Last 2 Months

[@swapnab4453]

Hi.. we have two slicers in our report. Need to disable week slicer when daterange slicer is filtered. I used two measures. Check Filtered = ISFILTERED(date[Date]) and Hide visual = if ([check filtered]=TRUE,1,0). I used hide visual as a filter in week slicer (is 1). But it’s not disabled when I filter daterange slicer. Any idea?

[@cainweo]

This maybe a stupid question but what about if i want to set a LAST week ( sunday to satursday ) option ? do i need to set up a time range ?

[@TechReviewFR9]

Hi thanks for the video. I have a question, how can I synchro that so that when i change the date in a page it changes in all bookmarks???

[@trainernoorani]

This is brilliant. Youre genius

[@BIElite]

Haha glad to hear, Ali! I like this one too.

[@davidezam]

Thanks a lot . But I've a problem .I want to use the date range selected (with your slicer) with some Time intelligence function. For example I need to retrieve the sum of page Views for the same period of previous year : Calculate(SUM(metrics[pageViews]),SAMEPERIODLASTYEAR('Date'[Date])) . I've to change the type of join between the 2 tables Date Period and Date to have the right result. Can you help me ??? There's a solution ?

[@Mrglasshalfempty]

Dude that is AWESOME -- thanks!!

[@BIElite]

Glad to hear :)

[@davidcarr7793]

This is great but can you have it clear the custom date range when another option is selected?

[@manojprabhakar7951]

Also I have duplicated my date&time field and changed the format to date and it worked, but mine is ivr contact center data so i want this to work with my date&time field , when i tried with date&time field it throws error message like you have duplicate date's

[@funzone-ct5qg]

date slicer is not getting disabled based on the filter . any tips please?

[@chronostinger2011]

Great tutorial! Thanks a lot!

[@BIElite]

No problem Richard!

[@FredMartino]

Good idea but needs to be updsted the dax gives me an error on 'type'?

[@greggpambianco1691]

I would like to create custom groups such as "Fiscal Year" which may run May-Apr. How can I incorporate this?

[@abhishekparihar1]

Hey Great article for Custom date - makes life easy

[@Tube-id7su]

Its very amazing... Can i get to show the data like stock exchange such as 1m,5m,4h,1d,1M,1Y,5Y,MAX?

[@lokeshrajk653]

Hi, I have one issue here. I will select Custom in first slicer and change the values in the second slicer. Then again I will select "All time" in the first slicer, here my graph is still showing the values within the range I selected in the second slicer even though that second slicer is disabled. How do we reset the second slicer to use all dates when I uncheck custom in first slicer. Please help.

[@MrRohanroxx1]

Thank you so much for this videoo!! I needed this for my job ^_^

[@Harpreet06]

Is it possible to make a button with Last week and another button with Last Week with similar functionality as this?

[@govardhanbola1195]

I have similar kind of requirement. I have Time Period filter as "Monthly", "Weekly", "Annually" and Custom. If I select Monthly, on the axis it has to show rolling 12 months, and date format on the axis as Jan-2020, Dec - 2021, Nov-2021 etc. If I select Weekly, on the axis week end date should be shown and if it's Yearly, then rolling 3 years and on the axis, 2022, 2021, 2020 should be shown. Can you please help me with this

[@josesalgado6526]

What if I need to add a new tipe in the date period table: "Last 12 Months"? Notice that is different from "Last Year".

[@khardu]

Thanks for the great tip. However, my calendar table has dates in the future (until 31-Dec-2021). When I try to use MTD, it returns dates from Dec-2021. Same is the case with QTD and YTD. Could you please help with this.

[@Joy_Speculates]

You can try using Today function in the formula

[@BIElite]

Hi Khardu, I’ll have to do some digging into this. Your date table setup is pretty common, so a solution would be helpful to many! I’ll get back to you.

[@manish.nsYoutube]

@@BIElite I had the same issue and this is what i ended up doing. Date Periods = var todayDate = TODAY() var firstDateOfMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1) var Result= UNION( ADDCOLUMNS( CALENDAR(firstDateOfMonth, todayDate), "Type", "MTD", "Order", 3 ) ) return Result Not as elegant as using DatesMTD but after spending an hour trying ti figure it out went with the expanded dax code

[@khardu]

@@manish.nsKZbin I tried that by filtering the date table but when I try to create relationship between SlicerDateTable and OriginalDate table, Power BI doesn't allow giving error of circular dependency.

[@tinaflemons263]

@@manish.nsKZbin That worked for MTD, How did you resolve QTD?

[@hi_vishy]

Can this technique be used to disable/enable other visuals or slicers ? May be slicer with Country or States.

[@cameronking4807]

This is a great solution to many common problems. Is there a way to complete in Power Query? The reason I ask is that if I create reports I like to use the same date dimension and having to create this each time could be a bit time consuming. Even if there isn't this is a great solution! Thank you for posting all the great tips and tricks.

[@BIElite]

Hey Cameron, that's an interesting question. I'm not sure the answer. You bring up a good point about the usability of a PQ date table.

[@AbhishekSingh-qg7eh]

Thanks for the video.Now I am trying to take it a step further and calculate WeekToDate but seems like there is no built-in function like DatesWTD, So that's the workaround for that. How can we add week to date measure in that calculation?

[@da6800]

ADDCOLUMNS( DATESINPERIOD( DateDimension[Date], today()-weekday(today(),2), 7, DAY ), "Type" , "WTD", "Order", 2 ),

[@Amol51279]

If I have month-year on axis and if I am selecting mtd then it shows other months as well as 0.what is the work around for this

[@andreaspslk4894]

Hello! Is there any possibility to implement 'Last 7 Days' ?

[@PallaviChauhan91]

How can we add "Last Week" ?

[@talentspark-pk]

Hi, this is great. However, is there any way to gray out those dates on the CALENDAR (in the date picker) which are outside the available date range? If possible, it would be ever GREATER! Thanks.

[@squantrill]

use bookmarks to show and hide the preset dates list and date slider. create a button that jumps between. the bookmark can also reset both selections when you switch

[@meeradominic9094]

Hi..i tried this method and it made my datamodel so slow. Initially my date table was created using dax, and I changed it with a power query. After that also, it is very slow. Any idea?

[@santoshkamala2750]

Hi Parker, loved the way you explain. However, I have a challenge. In the above example - I am pretty sure you must have connect the dates through data model. What in case, in some situation where my date is disconnected to data model. Example below: I have two date columns in my table (Start Date and End Date). I want to give a calendar picker to my user in visualization page. Based on the date selected by my user, I want to calculate two columns: First column: "How many days completed?" Formula: Selected Date - Start Date + 1 Second column: "How many days to GO" ? Formula: End Date - Selected Date + 1 Is it possible ? Initially, I thought to keep dates as parameter. But we don't have DATE datatype. Please help.

[@romaman892002]

Thank you, very useful stuff

[@BIElite]

No problem!

[@carlscholl6519]

My data is organized by quarter for different years (IE March 30 YYYY June 30 YYYY September 30 YYYY December 31 YYYY) Is there a way to make the custom filter slider increment quarterly rather than by each day?

[@ramus880]

I tried this.. my mtd formula overwrites qtd .. how to handle this?

[@MD-kv2gc]

Hmmm. I tried this but it doesn't quite work for me - the filtering does, but the "disable" for the custom range doesn't - it doesn't grey it out, it doesn't prevent the user clicking the calendar icon and changing a date ... have I missed something?

[@ankitavishwakarma6866]

Thanks a lot...this video saved me

[@samaguire1]

Wicked! I love this. 👍

[@BIElite]

Thanks Stephen!

[@fcalastr0]

I just had a PowerBI lesson with Jim Halpert 😮

[@bennaniyusuf6845]

how to combinate sliders in one slider that change the time for 3 different charts

[@trich103]

The 'Date Slicer Filter' no longer works. Is there a work around?

[@joriskempen5650]

I'm trying to rebuild this in a small setting (1 fact table with 30 dates + visitors for day) and i have built the Date table + Date Dimension Table) but it doesnt want to filter on MTD or any other? What am i missing?

[@joriskempen5650]

Found it, it's the DIRECTION of relationship needs to be BOTH!

[@makeall.digital]

There is a little mistake. If you put DATEYTD for all previous options, the result didn't work properly. I made a little correction: ADDCOLUMNS( PREVIOUSYEAR(DATESYTD('Calendar'[Date])), "Type", "LY" ), ADDCOLUMNS( PREVIOUSMONTH(DATESMTD('Calendar'[Date])), "Type", "LM" ), ADDCOLUMNS( PREVIOUSQUARTER(DATESQTD('Calendar'[Date])), "Type", "LQ" )

[@ceruizcoll]

Is there any reason why "Last Month" and "Last Quarter" show 2023 data and not 2024? I did all the same steps here.