Date time (up to hour) range filter

Power BI
1

I need a way to filter a range of date + time filed up hour (not minute). I have been reading some posts in power BI community. Looks like this is not supported at this point.

I am wondering if anyone has found an alternate solution to solve this ? if so, do you mind to share ?

Basically, I need to be able to select
a) Begin Date + Start time (hour)
b) end date + end time (hour)

eg: Begin Date = 2019 - 03-01 , Start time = 3:00 AM
end date = 2019-03-02, end time = 6:00 PM

then I need to grab dataset falls in this time range only.

sample screenshot : - date time picker

image

2

Have you read these two articles?

https://ginameronek.com/2014/10/01/its-just-a-matter-of-time-power-bi-date-time-dimension-toolkit/

Hope this helps and if it doesn’t please supply a sample PBIX file so forum members can better assist you.

1 Like
3

Thanks Melissa for sharing this. Haven’t mess around with M script myself that much. But this will be a good start for me ! :slight_smile: I will be back for more help if needed…

4

Hi Melissa, after reviewing these blog posts in details. it’s the same issue that I am running into. I am not able to filter date + time in the correct range.

The filters (splitting the date & time tables), only allow me to select all dates where time starts at selected hour range only:-

For example:-
** Begin Date = 2019 - 03-01 , Start time = 3:00 AM**
end date = 2019-03-02, end time = 6:00 PM

Current proposed solution:
Dataset contains
2019-03-01 between 3:00 AM till 6:00 PM
2019-03-02 between 3:00 am till 6: PM.

Instead, I need the data starts from 2019-03-01 3:00 am , including 2019-03-01 7 pm, 2019-03-01 8 pm … also 2019-03-02 midnight , 2019-03-02 2 am … till 2019-03-02 6 PM.

>= 2019-03-01 3:00 am && <= 2019-03-02 6:00 PM
I need the full range … in between.

attached is the modified data model where
i) i have my calendar table built out. So I continue using it.
ii)dimTimehourOnly table contains hour info, excludes minute for a quick concept trial out.

Do you mind to guide if I understood the blog post correctly?

image
image.png1097Ă—770 33.2 KB

PAS-New- AccessNumber attempt - added open orders compute.pbix (971.5 KB)

image
image.png855Ă—818 45.5 KB

6

Run into the same problem I couldn’t find a way to make this work with slicers so I combined Date and Time and added that [Combined] field to the Filter pane. That does get you the desired records.

I don’t know if there’s a better way because you’re looking for a period that can exceed a single day.
Anyway let’s see how you get on with this.
PAS-New- AccessNumber attempt - added open orders compute (1).pbix (1.1 MB)

7

Thanks Melissa. Huh, the filter pane instead of regular filters/slicers. Thanks for your guidance. This is great. Let me pretty up the report. appreciate your help! :+1:

8

If you can find a slicer that allows you to filter in the same way that’s fine too :smiley:.

A few more things to think about:
Determine what level of granularity you need to report on. Is it by minute or by hour?
Make sure your Calendar only includes all days of all years you actualy need.
If you need to do any calculations, it’s always best to do that over a smaller dimension table than a larger fact table that could have many duplicates. Change your dimTime table to the granularity you need including the combination day+time and remove that combination from your fact table. Make the necessary relationships and save a copy of the file. Check if everything still works as expected (and compare file sizes this version should be smaller).

And as I mentioned before maybe there’s a better way to solve this I don’t know so let’s hope other members join in this discussion.

9

Thanks again Melissa. Will do. The report requirement is @ hour granularity.
I am not able to find any date time slicer myself. Same here, if other members has good recommendation, please post. Thanks! :+1: