Filter by active licenses based on the time slicer

[Anonymous]

Hi,

I have a table with a list of users, each with a start and end date for their subscription.

What I would like to add to reports is a way to filter the visuals just considering the users which had an active subscription depending on the calendar date slicer. 

So for example:

User A  has a start subscription date the 15/1/2022 and expiration subscription date the 15/5/2022. So if I choose March 2022 in my date slicer they should pop up, but if I choose June 2022 they shouldn't. 

How can I achieve it? Through a measure or column? and how can the DAX be?

[Anonymous]

@johnt75 It doesn't work, it returns 1 also when the license is expired. Any idea on why?

[johnt75]

can you post a screenshot ?

[Anonymous]

[johnt75]

Sorry, I meant a screenshot of the table visual showing the incorrect data

[Anonymous]

This User had a License start date in Sep 2021 and expired in Jan 2022, so before and after should be 0

[johnt75]

Is it possible for an email address to appear multiple times in keystone_users? that would prevent SELECTEDVALUE from working

[Anonymous]

No, every user appears max one time.

[Anonymous]

No, every user appears max one time.

[johnt75]

for debugging try changing the measure to return licenceStartDate and then licenceEndDate, see what that shows.

is there a relationship between calendar and keystone_users? if so, what is the cardinality and direction ?

[johnt75]

As it needs to react to slicers it will have to be a measure, not a column. You can create a measure like

User has active licence =
var minDate = MIN('Date'[Date])
var maxDate = MAX('Date'[Date])
var licenceStartDate = SELECTEDVALUE( 'Table'[Licence start date])
var licenceEndDate = SELECTEDVALUE('Table'[Licence end date])
return IF( licenceStartDate <= minDate && ( ISBLANK(licenceEndDate) || licenceEndDate >= maxDate ), 1 )

then put that as a filter on your table visual to only show when the value is 1