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.
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.
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!
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.
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 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.
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, 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
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" )
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
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
Thank you so much for sharing this, I'm new to power bi, Have been struggling on this for the past few weeks. My actual requirement is Today, Yesterday, This Week , Last Week , This Month, Last Month, This Quarter, Last Quarter, This Year, Last Year, Custom - Could someone please help me with the logic on Today, Yesterday, This Week, Last Week
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
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 ?
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
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.
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.
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?
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?
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
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.
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.
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.
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.
Not sure this method works everytime, after playing with the date slicer by entering data-range or move between the slider using the "Custom" option! The silder just frozen (Can't Move) after slected other "Type" option!
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?
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
Hi Parker, very useful video and very practical need everyone for every project ......very big thanks to share with us. If it can possible can you plzz provide us excel data file which you used. Very greatful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂
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
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?
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 :(
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?
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.
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.
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.nsRUclips 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 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!!
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.
How to add a period Named "Current Quarter - 2" - now this becomes the previous quarter -1 OR let's say current quarter -2 - how to achieve this in the same table
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 ?
Hello, how can I filter the date in the columns of a matrix to get the 1st or 2nd week of the selected month as a calendar? From Monday to Sunday. Thanks.
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
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.
date slicer
is not getting disabled based on the filter . any tips please?
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!
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.
Disabling the slicer impressed me! Thanks for the video :)
Thanks for watching!
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]
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.
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, 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
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
I love this video. This is the level of finesse I've been looking for in a Power BI user
2:32 I like that😍💋 💝💖❤️
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
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"
)
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
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
Thank you so much for sharing this, I'm new to power bi, Have been struggling on this for the past few weeks.
My actual requirement is Today, Yesterday, This Week , Last Week , This Month, Last Month, This Quarter, Last Quarter, This Year, Last Year, Custom - Could someone please help me with the logic on Today, Yesterday, This Week, Last Week
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
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 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 ?
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!! Can you please help me on how to get the "Previous Week" (last week) default slicer selection.
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
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.
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?
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.
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?
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?
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
Is there any reason why "Last Month" and "Last Quarter" show 2023 data and not 2024? I did all the same steps here.
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
power bi sucks so bad.. all that for a stupid date filter... Metabase man..
Great video, thanks Parker. Really like the measure to disable, learned something new there :)
Awesome to hear, Mark!
thanks for sharing. BTW can ad share how to create text "Choose a custom date range" in the top of the time range slicer?
The illustration of graph is too small for me to decipher what is being explained
How did you get dynamic heading change on custom date slicer?
The 'Date Slicer Filter' no longer works. Is there a work around?
how to combinate sliders in one slider that change the time for 3 different charts
This is a thing of beauty!
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.
Good idea but needs to be updsted the dax gives me an error on 'type'?
Awesome.. This is very straight forward and help to achieve more interactive dashboard.
Glad to hear that, Sumit 😊
How can we add "Last Week" ?
as a newcomer to power Bi this is gold, thank you!
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.
I replicated Date Periods but my MTD table just shows 1/12/23 - 31/12/23. My date table includes each date from 1/1/23 - 31/12/23
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.
date slicer is not going to default
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 😊
Amazing video.....Parker. How can i add last 1 week, Last 2 weeks, Last 1 month and Last 2 Months
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
Not sure this method works everytime, after playing with the date slicer by entering data-range or move between the slider using the "Custom" option! The silder just frozen (Can't Move) after slected other "Type" option!
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?
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
Hi Parker,
very useful video and very practical need everyone for every project ......very big thanks to share with us.
If it can possible can you plzz provide us excel data file which you used.
Very greatful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂
I would like to create custom groups such as "Fiscal Year" which may run May-Apr. How can I incorporate this?
Great Video, sir. What would be the changes, if i want to add 'last 10 days' option to this. Please advise
Thank you
Its very amazing... Can i get to show the data like stock exchange such as 1m,5m,4h,1d,1M,1Y,5Y,MAX?
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
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?
In power bi service date are not automatically updating..it's only showing the date when it's uploaded
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
Amazing trick, very helpful
Dude that is AWESOME -- thanks!!
Glad to hear :)
I tried this.. my mtd formula overwrites qtd .. how to handle this?
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?
great video, how can we make another slicer which could control daily, monthly & quarterly
Thanks for Video, Can this be done dynamically (using slicer date value , instead of today() in calculated table).
Great video, I downloaded the pbix too and set this up successfully in my data model. Thank you! I engage with this content.
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?
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.
Is it possible to make a button with Last week and another button with Last Week with similar functionality as this?
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 ?
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!
Thank you so much for this videoo!!
I needed this for my job ^_^
Thank you so much for the video. If I want to show last 13 months, could you please explain how to do that?
I just had a PowerBI lesson with Jim Halpert 😮
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.nsRUclips 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.nsRUclips That worked for MTD, How did you resolve QTD?
Hello! Is there any possibility to implement 'Last 7 Days' ?
What if I need to add a new tipe in the date period table: "Last 12 Months"? Notice that is different from "Last Year".
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
This is brilliant. Youre genius
Haha glad to hear, Ali! I like this one too.
This is great but can you have it clear the custom date range when another option is selected?
Thank you, very useful stuff
No problem!
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!!
Can this technique be used to disable/enable other visuals or slicers ? May be slicer with Country or States.
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.
How to add a period Named "Current Quarter - 2" - now this becomes the previous quarter -1 OR let's say current quarter -2 - how to achieve this in the same table
Please help!!
Any update guys!
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 ?
Wicked! I love this. 👍
Thanks Stephen!
Hi Parker, I appreciate it very much, it is so useful, and I have it in my Power Bi Master file
looks like so practical, thank you !
Glad to hear it!
Hello, how can I filter the date in the columns of a matrix to get the 1st or 2nd week of the selected month as a calendar? From Monday to Sunday. Thanks.
really nice
Great tutorial! Thanks a lot!
No problem Richard!
Thank You
Thank you so much for this trick. Power Bi Time visuals are really somewhat confusing to setup. This will be helpful.
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???
Thanks a lot...this video saved me