Date and Time slicer from Calendar table

[ni30]

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.

[jinma78]

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

[v-jiascu-msft]

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

... ...

Best Regards,

[jinma78]

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.

[v-jiascu-msft]

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,

[jinma78]

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

Regards

[v-jiascu-msft]

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 ) )

Best Regards,

[ni30]

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.

https://www.dropbox.com/s/h7ue7lcyaz487t9/Test_Table.pbix?dl=0

[itsme]

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.

[v-jiascu-msft]

Hi @ni30,

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

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.

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".

Please also download the demo from the attachment.

Best Regards,