Date slicer value capturing and using in filtering data

[Anonymous]

Hi,

We have four date columns and we need to compare the date selected from the date slicer with these four columns and then create the visuals which will  filter accordingly.

E.g I have four date fields Date1,Date2,Date3 and Date4. If a user select Date from Calendar table, the report and visuals should get filtered on following condition:

Date 1 <= <SelectedDate> AND

Date 2 >= <SelectedDate> AND

Date 3 <= <SelectedDate> AND

Date 4 >= <SelectedDate> 

I am able to capture the date selection using ALLSELECTED function but unable to use the selected value in creating a calculated column that can help in filtering the dataset and visuals.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

Add below measure into "Visual level filter" and set its value to 1.

check =
IF (
    MAX ( [Date1] ) <= SELECTEDVALUE ( 'calendar'[Date] )
        && MAX ( [Date2] ) >= SELECTEDVALUE ( 'calendar'[Date] )
        && MAX ( [Date3] ) <= SELECTEDVALUE ( 'calendar'[Date] )
        && MAX ( [Date4] ) >= SELECTEDVALUE ( 'calendar'[Date] ),
    1,
    0
)

Best regards,

Yuliana Gu

[Anonymous]

Thankyou for the above solution. It works fine when we are dragging those date columns into the visual. But we want the count of other columns as well with the date filtered in different visuals where we do not wish to drag the 4 date columns. Suppose I drag a dimension and measure from dataset, apply this measure filter and create a visual like bar chart/line chart etc. then no data appear on the visual and the visual goes blank. Could you help me?