I rarely ever comment on a TH-cam 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!
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
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.
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!
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.
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.
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?
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.
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)
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"?
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
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
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
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.
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.
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
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?
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 ?
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.
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?
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?
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
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
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.
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
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
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.
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.
@@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
@@manish.nsTH-cam 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.
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 ?
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?
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...
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...
@@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.
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?
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?
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.
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!!
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?
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.
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
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.
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.
This one is not working when I change the custom date range to some value which is not current month , then when I switch to MTD it’s showing blank values
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 :(
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.
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.
Hi, a great way to dynamically filter date selection. Though I have I problem when I for example want to show 'the selected period' vs 'the same period last year' in a visualization. If I selected the MTD, I want I visualization to show the Sales for MTD and the Same period MTD last year. I use this function to get the SPLY, but it does not show on the viz when I use your date filtering (only if I select the "all-time" option: Turnover SPLY = CALCULATE( [Turnover], SAMEPERIODLASTYEAR(dim_calendar[Date])) Anyone who knows how to fix this?
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.
Hi Hayven, I meant to mention in the video but you might want to add a "Reset filters" button or message to tell the user to click the eraser on the custom date slicer.
@@BIElite I've been trying to research this, but is it possible to add a "Reset Filter" for these specific layouts upon changing selection? My end-users will probably find this confusing when choosing to go with the custom dates ... Since it is a popular option because it gives some freedom of choice :). Curious to your reply! Thanks.
@@hayveniylia1799 I agree...I was actually going to demo this tomorrow as an example to other developers but I don't think it works as intended and its quite intuitive enough. The problem is if you go to custom dates and pick this month for example, but then go and try and pick "Last Year" from the new dropdown, most users would expect it to just overwrite this month and pick all of last year. However as it's currently setup it wouldn't really work that way.
I love this video. This is the level of finesse I've been looking for in a Power BI user
Perfect. Thanks, been trying to do this for more than 4 days now.
I rarely ever comment on a TH-cam 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!
as a newcomer to power Bi this is gold, thank you!
Disabling the slicer impressed me! Thanks for the video :)
Thanks for watching!
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
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.
Hey did you find any solution to it?
@@chandanamb9061 Metabase
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!
Thank you for providing this simple and perfect DAX, hope will get more helpful videos.
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.
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.
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?
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.
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)
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
My jaw is still on the floor lol. Just talked to a client about this today. Thanks a bunch.
Haha I love when the timing works out like that 😊
Thats really awesome !! Going to implement in the report right now ! Thanks a ton for sharing this !!
Glad to hear, Abhishek! Hope it turns out well.
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"?
Great video, I downloaded the pbix too and set this up successfully in my data model. Thank you! I engage with this content.
date slicer
is not getting disabled based on the filter . any tips please?
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
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
Awesome.. This is very straight forward and help to achieve more interactive dashboard.
Glad to hear that, Sumit 😊
Great video except there's no last week option how would you add this? Thanks
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
How did you get dynamic heading change on custom date slicer?
Thank you so much for the video. If I want to show last 13 months, could you please explain how to do that?
Great Video, sir. What would be the changes, if i want to add 'last 10 days' option to this. Please advise
Thank you
great video, how can we make another slicer which could control daily, monthly & quarterly
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.
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.
Great video!! Can you please help me on how to get the "Previous Week" (last week) default slicer selection.
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
I tried this.. my mtd formula overwrites qtd .. how to handle this?
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?
Hi Ulad C, i am having the same problem. Did you find any good solution for this? :)
@@JosefineNord Yep) Try to use Crossfilter function in order to deactivate the relation for the "Previous" measures .
@@Iasdsfdsdaerbrt Can you explain how?
Good idea but needs to be updsted the dax gives me an error on 'type'?
Nice video!, i would like to know how also add "Last 2 week" and "Last x Days" in the same column "Type" . Thks
DID YOU GET A SOLUTION FOR THIS PLEASE
Is it possible to make a button with Last week and another button with Last Week with similar functionality as this?
thanks for sharing. BTW can ad share how to create text "Choose a custom date range" in the top of the time range slicer?
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
Can anyone tell me how create for Current Month also?
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 ?
Great video, thanks Parker. Really like the measure to disable, learned something new there :)
Awesome to hear, Mark!
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?
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 ?
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.
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?
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?
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
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
I'm stuck, can you explain this more?
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.
I would like to create custom groups such as "Fiscal Year" which may run May-Apr. How can I incorporate this?
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
did you ever figure this out. I am having the same issue
How can we add "Last Week" ?
Thank you so much for this trick. Power Bi Time visuals are really somewhat confusing to setup. This will be helpful.
Thanks for Video, Can this be done dynamically (using slicer date value , instead of today() in calculated table).
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???
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
What if I need to add a new tipe in the date period table: "Last 12 Months"? Notice that is different from "Last Year".
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.
You can try using Today function in the formula
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.
@@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
@@manish.nsTH-cam 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.
@@manish.nsTH-cam That worked for MTD, How did you resolve QTD?
This is great but can you have it clear the custom date range when another option is selected?
Just what I was looking for. Fantastic.
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 ?
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?
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...
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...
@@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.
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?
Found it, it's the DIRECTION of relationship needs to be BOTH!
The 'Date Slicer Filter' no longer works. Is there a work around?
This is a thing of beauty!
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!
Hello! Is there any possibility to implement 'Last 7 Days' ?
how to combinate sliders in one slider that change the time for 3 different charts
Is there any reason why "Last Month" and "Last Quarter" show 2023 data and not 2024? I did all the same steps here.
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?
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.
It is. I tried and it works for last month.
Yes, it doesn't work with DATESYTD, only with DATESMTD. Similar with Last QtR [DATESQTD]
Can this technique be used to disable/enable other visuals or slicers ? May be slicer with Country or States.
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!!
That was AWESOME! Wow. I have done something similar but not near as elegant and inclusive. THANK YOU!
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?
ADDCOLUMNS(
DATESINPERIOD(
DateDimension[Date],
today()-weekday(today(),2),
7,
DAY
),
"Type" , "WTD",
"Order", 2
),
Amazing video.....Parker. How can i add last 1 week, Last 2 weeks, Last 1 month and Last 2 Months
Amazing trick, very helpful
You are amazing, Parker!
Thanks Ramesh!!
looks like so practical, thank you !
Glad to hear it!
In power bi service date are not automatically updating..it's only showing the date when it's uploaded
Its very amazing... Can i get to show the data like stock exchange such as 1m,5m,4h,1d,1M,1Y,5Y,MAX?
fantastic job , thank you for making our lives easier :)
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.
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
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.
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.
Did you find the solution for PY, SAMEPERIODLASTYEAR?
You need calculation groups for it using tabulareditor
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
This is brilliant. Youre genius
Haha glad to hear, Ali! I like this one too.
This one is not working when I change the custom date range to some value which is not current month , then when I switch to MTD it’s showing blank values
Yes, this doesn't work . Did you resolve this?
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 :(
Yes, you’re exactly right! The dynamic titles add a little bit of touch
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.
Hi Parker, I appreciate it very much, it is so useful, and I have it in my Power Bi Master file
Dude that is AWESOME -- thanks!!
Glad to hear :)
Great tutorial! Thanks a lot!
No problem Richard!
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.
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.
Hey Great article for Custom date - makes life easy
Hi, a great way to dynamically filter date selection.
Though I have I problem when I for example want to show 'the selected period' vs 'the same period last year' in a visualization.
If I selected the MTD, I want I visualization to show the Sales for MTD and the Same period MTD last year.
I use this function to get the SPLY, but it does not show on the viz when I use your date filtering (only if I select the "all-time" option:
Turnover SPLY =
CALCULATE(
[Turnover],
SAMEPERIODLASTYEAR(dim_calendar[Date]))
Anyone who knows how to fix this?
I encountered the same issue, where you able to find a solution? Thanks!
Thank you so much for this videoo!!
I needed this for my job ^_^
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.
Hi BI,
If you choose your custom date range to be anything from the minimum and then change your option to All Time, the filter stays on there.
Hi Hayven, I meant to mention in the video but you might want to add a "Reset filters" button or message to tell the user to click the eraser on the custom date slicer.
@@BIElite I've been trying to research this, but is it possible to add a "Reset Filter" for these specific layouts upon changing selection?
My end-users will probably find this confusing when choosing to go with the custom dates ... Since it is a popular option because it gives some freedom of choice :).
Curious to your reply!
Thanks.
@@hayveniylia1799 I agree...I was actually going to demo this tomorrow as an example to other developers but I don't think it works as intended and its quite intuitive enough. The problem is if you go to custom dates and pick this month for example, but then go and try and pick "Last Year" from the new dropdown, most users would expect it to just overwrite this month and pick all of last year. However as it's currently setup it wouldn't really work that way.
@@mcnater
Hi, have you found any good solution for this? Or is the only way to solve it using a reset button?