Custom Date Period Selections in Power BI
Вставка
- Опубліковано 21 лип 2024
- In this Power BI tutorial, you'll learn how to build a date period selection slicer! The cool part of this trick is you can set a "Custom" option that allows the user to select a custom date range, rather than the pre-defined date ranges. This custom date range selection is disabled until the "Custom" option is selected!
To enroll in my introductory or advanced Power BI courses:
training.bielite.com/
Blog Post/PBIX:
bielite.com/blog/custom-date-...
Elite Power BI Consulting:
bielite.com/
Data Insights Tools:
www.impktful.com/
Connect with me on Twitter!
/ powerbielite
0:00 Demo of finished product
1:33 Creating pre-defined date periods
7:54 Adding "Custom" selection
10:24 Outro - Наука та технологія
I rarely ever comment on a UA-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!
I love this video. This is the level of finesse I've been looking for in a Power BI user
as a newcomer to power Bi this is gold, thank you!
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.
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?
Disabling the slicer impressed me! Thanks for the video :)
Thanks for watching!
That was AWESOME! Wow. I have done something similar but not near as elegant and inclusive. THANK YOU!
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.
Thank you so much for this trick. Power Bi Time visuals are really somewhat confusing to setup. This will be helpful.
Great video, I downloaded the pbix too and set this up successfully in my data model. Thank you! I engage with this content.
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! 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"?
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.
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
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.
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.
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 ?
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 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!!
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, thanks Parker. Really like the measure to disable, learned something new there :)
Awesome to hear, Mark!
Just what I was looking for. Fantastic.
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.
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 ?
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
Great Video, sir. What would be the changes, if i want to add 'last 10 days' option to this. Please advise
Thank you
Hi Parker, I appreciate it very much, it is so useful, and I have it in my Power Bi Master file
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
Thanks for Video, Can this be done dynamically (using slicer date value , instead of today() in calculated table).
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 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.
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.
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.
Amazing trick, 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?
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
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
Awesome.. This is very straight forward and help to achieve more interactive dashboard.
Glad to hear that, Sumit 😊
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!
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
great video, how can we make another slicer which could control daily, monthly & quarterly
What if I need to add a new tipe in the date period table: "Last 12 Months"? Notice that is different from "Last Year".
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 😊
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
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?
Can this technique be used to disable/enable other visuals or slicers ? May be slicer with Country or States.
Thank you so much for this videoo!!
I needed this for my job ^_^
How did you get dynamic heading change on custom date slicer?
Its very amazing... Can i get to show the data like stock exchange such as 1m,5m,4h,1d,1M,1Y,5Y,MAX?
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?
fantastic job , thank you for making our lives easier :)
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 sharing. BTW can ad share how to create text "Choose a custom date range" in the top of the time range slicer?
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 ?
looks like so practical, thank you !
Glad to hear it!
Is it possible to make a button with Last week and another button with Last Week with similar functionality as this?
This is great but can you have it clear the custom date range when another option is selected?
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.
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!
Good idea but needs to be updsted the dax gives me an error on 'type'?
Great tutorial! Thanks a lot!
No problem Richard!
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?
Hey Great article for Custom date - makes life easy
I would like to create custom groups such as "Fiscal Year" which may run May-Apr. How can I incorporate this?
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
date slicer
is not getting disabled based on the filter . any tips please?
Hello! Is there any possibility to implement 'Last 7 Days' ?
You are amazing, Parker!
Thanks Ramesh!!
Dude that is AWESOME -- thanks!!
Glad to hear :)
I tried this.. my mtd formula overwrites qtd .. how to handle this?
Amazing video.....Parker. How can i add last 1 week, Last 2 weeks, Last 1 month and Last 2 Months
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
In power bi service date are not automatically updating..it's only showing the date when it's uploaded
How can we add "Last Week" ?
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
Great tip. Thanks
This is brilliant. Youre genius
Haha glad to hear, Ali! I like this one too.
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
),
Thank you, very useful stuff
No problem!
LOVE LOVE LOVE THIS!
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, 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
how to combinate sliders in one slider that change the time for 3 different charts
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!
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
Thanks a lot...this video saved me
Excellent great help
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.
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.
really nice
Thank you!
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]
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
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
Wicked! I love this. 👍
Thanks Stephen!
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 Tip! Tks!
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
The 'Date Slicer Filter' no longer works. Is there a work around?
Is there any reason why "Last Month" and "Last Quarter" show 2023 data and not 2024? I did all the same steps here.