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
07-17-2018 12:33 PM - last edited 07-18-2018 06:35 AM
In response to a Forum post here is my solution for adding a Date Time Range Slicer. It's not great but I've not found a better solution with the current version (Nov17)
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.
eyJrIjoiNmQ4MTdjN2UtZTdmYy00ZDRiLTgzNWUtOWNmNzgwOWQ4OTljIiwidCI6ImRkYmM2NDllLWJlMzEtNDRiOC05MzlkLTVjMGFmNGRiOTNiNSIsImMiOjh9
@stretcharm Do you think your work around would work for just a time filter. My date already has a slicer that works well. I now just need to implement your method on my time values. The values are in hh:mm format which I changed into time format. I think then I will only need to use decimals is that correct?
Any help would be greatly appreciated.
Thanks,
Amirreza