cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ni30
Frequent Visitor

Date and Time slicer from Calendar table

Hi,

 

 

I have a Calendar table with date and time in my report. My requirement is to create slicers for a date, hour and min for both start and end.

Currently, I am able to filter the data by start and end date. But I am not able to filter by start and end hour or min. I tried to create two Calendar tables for start and end hours and mins. Nothing is working. Can anyone help me with this issue.

 

Capture.PNG

9 REPLIES 9
jinma78
Advocate II
Advocate II

I have the same issue.  I think having a comprehensive calendar which also has timestamps as unix and converting the startdate time and enddate time to unix time stamp might be answer but not sure.... For example, stardate time will convert to unix time of 1548897004 and enddate time will convert to unix time of 1548898005 since epoch and so your slicer will get all data between these two unixtime 1548897004 to 1548898005.  To do this, your calendar will have to have a unix time since epoch as a column.  

 

The next trick to figure out is, how a UI input which is shown as date, hour, min can be used to control a slicer which is filtering by unixtimestamp?  For usability, we don't want the user to work in unixtimestamp as a slicer input.  Or is their a more direct way to filter the data without using slicer widget?

 

I appreciate if anyone can either validate this solution or suggest a different one.

 

Thanks

Hi @ni30,

 

@jinma78 already provides wonderful ideas. 

Regarding using Unix time, I would suggest adding two cards to show the start and end time in Datetime format, which is easy for us to read.

Regarding using slicers of hours and minutes, please download the demo from the attachment. Don't establish any relationships to the fact table. If you have a large number of measures to create, this idea isn't that smart.

There is still another way that we can create all the possible date time like below.

2019-01-01 00: 00

2019-01-01 00: 01

2019-01-01 00: 02

2019-01-01 00: 03

... ...

Date-and-Time-slicer-from-Calendar-table

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For me using a slicer to select hours and minutes is not a good option because I can not select for example 1pm to 10am from yesterday to today as start hour becomes greater than end hour.

Hi @jinma78,

 

That's really a problem. It seems we have to add more tables. The main idea of this workaround is capturing the date in a measure. 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi v-jiascu-msft
Can you provide an example as to what you mean by creating more tables?

Regards

Hi @jinma78,

 

Two tables for Hour and two tables for Minute. I would suggest creating two time table instead. Please refer to the snapshot below.

startTimeTable =
GENERATESERIES ( TIME ( 0, 0, 0 ), TIME ( 23, 59, 0 ), TIME ( 0, 1, 0 ) )

Date-and-Time-slicer-from-Calendar-table2

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

 

Thank you for your response. I am still not able to filter using start and end time tables as you have mentioned. When I select the End time the data table vanishes. Please find an example as below. Can you please advise how do I get the data filtered for time.

 

With Start Time Selected.PNGWith End time selected.PNGhttps://www.dropbox.com/s/h7ue7lcyaz487t9/Test_Table.pbix?dl=0

In this .pbix example (OneDrive shared file), I used data from timeanddate.com that shows daily sunrise and sunset times in Seattle and Anchorage (so there's more than one time period per day) for Feb - Apr.

 

There are four tables that will contain the values in each slicer. The slicers are for StartDate, StartTime, EndDate and EndTime (StartTime represents sunrise time and EndTime represents sunset time...you can adjust these to the appropriate beginning/end of selected time period in your dataset). Do not create relationships between any of these tables and the fact table.

 

Then add the measure below to the visual level filter and set it to is not blank (in this example the visual is the table). Hope this helps.

 

Measure_FilteredValues.png

Report.png

 

Hi @ni30,

 

1. The [Time] isn't a pure time in your demo. Please refer to the snapshot below. It should be [Time 2].

Date-and-Time-slicer-from-Calendar-table3

2. Since the Date and Time are two columns, I would suggest you make some changes like below. Remove one table and delete several relationships.

Date-and-Time-slicer-from-Calendar-table4

3. Create a measure.

Measure =
VAR startTime =
    MIN ( startTimeTable[Value] )
VAR endTime =
    MAX ( endTimeTable[Value] )
RETURN
    IF (
        MIN ( 'Sheet1'[Time 2] ) >= startTime
            && MIN ( Sheet1[Time 2] ) <= endTime,
        1,
        BLANK ()
    )

4. Add the measure in the Visual Level Filter and set it as "is not blank".

Date-and-Time-slicer-from-Calendar-table5

Please also download the demo from the attachment.

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors