Forum Discussion
Date and Time slicer from Calendar table
Advocate III 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-msftMicrosoft Employee
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,
- jinma78For 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,