Date and time input - slicer - fix time

[Anonymous]

Hello PBI Users,

Please help me if you know how to solve this:

I have a column which I imported using a direct query that has date and time values in a single column like this:

2018-07-10 4:01:36 PM

I want to use these values to let the user choose the date using a slicer with the date as input. For example, if the user selects 2019-01-01, I want to select all rows which have a date-time value between 2019-01-01 4:00:00 AM and 2019-01-02 4:00:00 AM. 

Another example: If I select 2020-01-27 - it should include results when 2020-01-27 4:00:00 AM <= DTM < 2020-01-28 4:00:00 AM

[Anonymous]

Hi @Anonymous ,

Please check the following steps.

1# Create a calendar table.

Table 2 = CALENDARAUTO()

 2# Create a measure.

Measure = 
var a = FORMAT(SELECTEDVALUE('Table'[date]),"YYYYMMDDHHmmSS")
var b = FORMAT(SELECTEDVALUE('Table 2'[Date]),"YYYYMMDD")&"040000"
var c = FORMAT(DATEADD('Table 2'[Date],1,DAY),"YYYYMMDD")&"040000"
return
IF(NOT(ISFILTERED('Table 2'[Date])),1,IF(a>=b&&a<c,1,BLANK()))

3# Add the measure to visual level filter.

Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

@Anonymous 

Thank you, Jay. I just created a table and the measures. I selected table 2 and then when I check the list view I get dates along with the time field. How can I remove the time field, and, would selecting dates from this table filter the results in other visuals? 

Please help!

[Anonymous]

Hi @Anonymous ,

1# Select the column and click Modeling -> Format.

2# No, it won't, but you can use SELECTEDVALUE() function as below measures.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

@Anonymous 

Thank you, the format worked to change the list. However, selecting the dates on table 2 does not change the other visuals according to the date as any other slicer would do? Do you know how to make this work? Thank you again 🙂

[Anonymous · Accepted solution]

Hi @Anonymous ,

You will need a relationship between table2 and other visuals or use SELECTEDVALUE() function.

Please check the two visuals of this pbix, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

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