Slicer on SQL table with Begin and End Date

Hey,

 

basically this is not as simple as it should be / could be, this is due to the following

• currently it's not possible to add a measure to the Visual level filter of the default slicer
• currently it's not possible to add a measure to page or report level filter

Nevertheless my solution needs an additional table with date values, this table is not related to any date column of the customer table. This table is used to select a date range, e.g. by using the date slicer.

 

Then I created a measure that returns the value 1 if the customer can be considered during the selected time period:

Check Active Customer = 
var minDate = MINX(ALLSELECTED('Calendar'[Date]),'Calendar'[Date])
var maxDate = MAXX(ALLSELECTED('Calendar'[Date]),'Calendar'[Date])
return
SUMX(
    'Customer'
    ,
    var custStartdate = 'Customer'[Startdate]
    var custEnddate = 'Customer'[Enddate]
    return
    IF(
        AND(custEnddate >= minDate, custStartdate <= maxDate)
        ,1
        ,BLANK()
    )
)

Here is a screenshot of a little report I created:

 

I'm using 

• Default Slicer (does show all Customer)
• Attribute Slicer (here the measure is usde on the as value)
• A bar chart

Personally my favorite solution is the "simple" bar chart, because here i can use the measure inside the visual level filter, this means I can use addtional measures to "provide" additional information about the customer.

 

The Disadvantage using a bar chart that the user of the report has to be accustomed to use the CTRL Key, if cross filtering has 

to be used.

 

On the other hand "formatting" of the Attribute Slicer becomes more complex.

 

Hopefully this gets you started,

 

Regards,

Tom