Use a date slicer to filter on a period instead of a single date

[Zahoul]

Hello,

I face an issue in my report.

Here is an example of a dataset I have (dates are in dd/MM/yyyy):

Code	Start date	End date	Title
1	10/01/2019	20/01/2019	My first loan
2	25/12/2018	12/01/2019	My second loan
3	28/12/2018	05/02/2019	My third loan
4	21/01/2019	08/02/2019	My fourth loan

I use a date slicer (based on a Calendar table) and the calendar table is linked to the Start date of my dataset.

If I choose 01/01/2019 - 31/12/2019 in the date slicer, the system will return line 1 and 4.

But in my case I want all loans that live in the period. All 4 loans exist at some point in january.

However my date slicer can be linked to one column only and I don't see how I could manage to have all 4 results with a single date slicer.

Would there be a way to achieve this?

Many thanks in advance,

Julien

[v-chuncz-msft · Accepted solution]

@Zahoul,

Disable the relationship, drag measure below to Visual level filters and set Show items when the value is 1.

Measure =
IF (
    SELECTEDVALUE ( Table1[Start date] ) <= MAX ( 'Calendar'[Date] )
        && SELECTEDVALUE ( Table1[End date] ) >= MIN ( 'Calendar'[Date] ),
    1
)

[Anonymous]

Hi @v-chuncz-msft ,

Can the measure be done without the SELECTEDVALUE function? I don't have it in my tabular version...

[AlB]

Hi @Anonymous 

Instead of this:

SELECTEDVALUE ( Table1[Start date] )

try this, which should be equivalent:

IF ( HASONEVALUE ( Table1[Start date] ), VALUES( Table1[Start date]) ) 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Zahoul]

Hello Sam,

Thank you very much for your help, it seems to do the job!

Julien