The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Dears,
I have the question that you can see in the subject - How can I filter data from datetime to datetime?. I want to filter date from YYYY-MM-DD HH:MM to YYYY-MM-DD HH:MM. Is it possible? I have to show car routes history on the map and I need it.
I please you also to send me instruction for other filter ways if descibed above is not possible.
Thank you.
There is no built-in time slicer currently. @stretcharm's workaround seems to be a good way.
You can also vote on related ideas in this link - https://ideas.powerbi.com/forums/265200-power-bi-ideas?query=time%20slicer
Best Regards,
Herbert
May be I didnot get you exactly. Do you want to filter the data based on DATETIME field..?
You can drop the column into a slicer directly or create a column.
I want to make filters using this:
In the Query editor you can goto the filter icon on the column header. Select the Date/Time Filters menu and then custom
You can then add a range.
The M code looks like this
#"Filtered Rows" = Table.SelectRows(#"Expanded Executables", each [StepEnd] >= #datetime(2017, 10, 17, 19, 45, 0) and [StepEnd] < #datetime(2017, 10, 17, 19, 55, 0)) in #"Filtered Rows"
Against a page/report or visualisation you can use the advanced filtering
I don't know an easy way of doing slicers to filter datetimes.
Phil
Even tought you get time field from all date time fields, how can you filter the data..?
The above datetime has some time values. Does your data contain the same for previous day also.
There is no way to do a datetime slicer and a range of date time is even worse.
I've played with a few options, but not got any to work particularly well.
There is a custom visualisation that does a time brush filter which would be good but it also doesn't use time.
The best I've got is using What If Parameters, using decimal parameters as a Datetime is stored as a decimal value with whole numbers being days and the decimals for the time.
There is no what if for date time so you have to use decimal
You can then edit the definition from
FromDateTime = GENERATESERIES(1, 20, 0.1)
To somethat that has range related to your data.
FromDateTime = GENERATESERIES(INT(MIN(Data[StartDateTime])), INT(MAX(Data[EndDateTime]))+1, 0.01)
You can also set the default dymanically
ToDateTime Value = SELECTEDVALUE(ToDateTime[ToDateTime], INT(TODAY())-1)
Create a new Computed Column to show the date time version
SelectedToDateTime = [ToDateTime Value]
Change this in modelling to a datetime with your preferred format. You can put this next to the slicer which is still show just a decimal value.
Sliding the slicer will update the value, but you've got to do a bit of trial and error to get the value you need.
Do the same for the ToDateTime
Now in the data set add a new Measure to check if the records are in the range specified by the slicers
IsInRange = if(min(Data[StartDateTime])>=FromDateTime[FromDateTime Value] && Max(Data[EndDateTime])<=ToDateTime[ToDateTime Value],1,0)
Finally add a filter to your visualisations or the page to only include values where IsInRange is 1
Its tricky to get the granualrity right for the time and slicers dont work well with large date ranges.
I've added my Solution to the How to Gallery
http://community.powerbi.com/t5/Data-Stories-Gallery/How-To-DataTime-Range-Slicer/m-p/306357#M1255
Hope this helps
Phil
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
80 | |
78 | |
43 | |
37 |
User | Count |
---|---|
158 | |
111 | |
64 | |
59 | |
54 |