Slicer on SQL table with Begin and End Date

[stack23]

I have a customer table that contains:

1. CustomerId
2. StartDate
3. EndDate (Blank willl be active)

I would like to create a slicer that can list all customers that were active between two dates. I want to conduct this efficiently Without enumerating. This example only uses enumeration: 

https://community.powerbi.com/t5/Desktop/Active-Employee-slicer-based-on-Start-and-End-date/td-p/350...

[TomMartens · Accepted solution]

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

[stack23]

this is wonderful,

I should have reclarified my question,  user places one date, and check if that date is between CustomerBegindate and CustomerEndate

example, report user selects: 3/5/2018

it will pickup anything between begindate <= 3/5/2018 <= enddate,

I assume this will work also?

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

[TomMartens]

Sure,

please do not forget to mark my / your post as answer, it will help others.

Regards,

Tom

[stack23]

will do, one more question, I am using a date dimension table, 

I cannot search for a date,

1) either I have to scroll down a long list or use

2) a between filter, and input the single date twice?

Is there any way I can just enter one date in the slicer?

[TomMartens]

Hey,
I'm not sure if the default slicer provides an "input" field, but maybe one of the different options available if you switch through

provides something more appropriate for you requirement.

Maybe you can also check the Calendar / Date visuals available in the marketplace.

Regards,

Tom

[stack23]

by the way, if we have 50 million rows, do you think this is the most scalable way to conduct calculations in a new column? or do you have a more efficient/faster method? I can post another question, let me know and send points.  I don't think enumeration method is scalable either.  Is there a third method for larger data sets?