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

Slicer - Date and Time

Struggling with a slicer issue, my data has a date and time stamp in the format "mm/dd/yyyy hh:mm AM" in my query editor, have have split the column to "mm/dd/yyyy" and "hh:mm AM" , so far so good. The data types are defined as "date" and "time"

 

In my report I have applied a date slicer which works perfectly, however my time slicer returns a date format in mm/dd/yyyy as opposed to hh:mm AM, so despite the data being collected and time stamped in the last 72 hours, the time slice is showing 12/30/1899........

 

Any ideas would be gratefully received.

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Sean Super Contributor
Super Contributor

Re: Slicer - Date and Time

@Stoo48You know I never switched the Slicer in my testing from List to any of the Before, Between and After Options!

 

It seems when you place a TIME column in the Slicer you can only use the List or Dropdown Options

 

I believe the others are only meant to be used with DATES

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-feature-summary/preview/#dateSlice...

 

So there's not really an issue with the Data Type and Format!

 

>>> The issue seems to be with Slicer's  Before, Between and After options not working with TIME Columns! <<<

 

Checkout this blog by @heathernicole @dbadmin

http://community.powerbi.com/t5/Community-Blog/Advanced-Time-Line-Slicer-Sorting-data-by-shifts-up-t...

She discussed the Advanced Time Slicer

 

Good Luck! Smiley Happy

Sorry that's probably not the answer you were we looking for.

You can report this as an ISSUE here

http://community.powerbi.com/t5/Issues/idb-p/Issues

View solution in original post

13 REPLIES 13
Sean Super Contributor
Super Contributor

Re: Slicer - Date and Time

Check in the Modeling Tab (not in the Query Editor since you've already set it there)

 

What is the DATA TYPE and FORMAT set in the Modeling tab?

Stoo48 Frequent Visitor
Frequent Visitor

Re: Slicer - Date and Time

Sean

 

Just checked, as the Query Editor, they are both showing correct TYPE and FORMAT in the modelling tab as I described above

 

 

 

 

Sean Super Contributor
Super Contributor

Re: Slicer - Date and Time

When you Import that column in the Query Editor (the original Date/Time column before you split it)

 

Does it default to Date/Time or Text?

 

Inside the column are the numbers left justified or right justified when you first import it?

 

And same for the 2 columns that you split the original into

 

Do they default to their appropriate Data Type in the Query Editor?

OR

Do you have to manually set the Data Types?

 

EDIT: Are you running the latest release?

https://www.microsoft.com/en-us/download/details.aspx?id=45331

Stoo48 Frequent Visitor
Frequent Visitor

Re: Slicer - Date and Time

Post Split.PNG

 

Columns, post split above^^^

Columns, pre split below

 

Pre Split.PNG

 

Regarding latest version, I am 1 release behind.........

 

 

Sean Super Contributor
Super Contributor

Re: Slicer - Date and Time

How do you split the original column?

 

select the original Column

Go to the Add Column tab => Date button => select Date Only

and then again same original column => Time button => Time Only

 

DateTime Column.png

 

I hope this resolves it! Smiley Happy

 

EDIT: I can't duplicate this but I've had issues with not being able to format dates... (but not with Slicers)

https://ideas.powerbi.com/forums/360879-issues/suggestions/15157260-date-field-used-as-map-legend-sh...

the above has been fixed

http://community.powerbi.com/t5/Issues/Nov-2016-Update-Colors-of-the-Map-Legend-no-longer-match-what...

this is still not

http://community.powerbi.com/t5/Desktop/Facet-Key-Custom-Visual/td-p/80087

and the Facet Key Custom Visual STILL does NOT work woith dates Smiley Sad

Stoo48 Frequent Visitor
Frequent Visitor

Re: Slicer - Date and Time

I used the split column function in the query editor, will try your method too

 

Split column.PNG

Stoo48 Frequent Visitor
Frequent Visitor

Re: Slicer - Date and Time

Apologies for the delay in feeding back, your suggestion on how to split the columns worked well, however the time slicer still shows up as dates from a century ago..........

 

Input:

Input.PNG

 

Output:

Output.PNG

 

Any thoughts and suggestions gratefully received...........

Sean Super Contributor
Super Contributor

Re: Slicer - Date and Time

@Stoo48  Can you duplicate the issue in a sample file and share the file? (via link to drop box or one drive)

Stoo48 Frequent Visitor
Frequent Visitor

Re: Slicer - Date and Time

@Sean I'll give it a go after lunch..........

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 765 guests
Please welcome our newest community members: